mysql 字符类型字段查询大小写不区分问题的解决方式

2021年8月23日 21点热度 0条评论 来源: EllisTian

前言:在实际的项目开发中,我们可能会遇到下面的问题,如下图所示,查询的时候,我传入的是"ZHANGSAN",但是查出来的结果中account却是zhangsan, 这种情况有可能导致意外发生,例如,我使用账户名:ZHANGSAN或者zhangSAN,都能登录系统,所以这种情况要解决。

问题分析:从上面的前言描述中,我们知道是因为mysql中字符类型的字段没有区分大小写的缘故,所以接下来就是分析为什么mysql没有区分大小写,或者怎么设置让mysql区分大小写。

1. 为什么mysql不区分大小写

    从mysql的官方文档中,可知,在mysql中“COLLATE ”这个属性控制大小写,而mysql中"COLLATE "核对属性的默认值是“utf8_general_ci”,是不区分大小写的

2. 怎么设置mysql区分大小写

     既然"COLLATE"属性不区分大小写,那么"COLLATE"肯定有属性是支持区分大小写的,事实确实如此。"COLLATE"属性中utf8_bin和utf8_general_cs都支持区分大小写。

其中utf8_bin表示二进制比较,同时也支持区分大小写,utf8_general_cs区分大小写。

但是:在Mysql5.6.10版本中,不支持utf8_genral_cs!

所以为了使得mysql支持大小写,有如下几种方案:

1. 在创建表的时候,指定表字段COLLATE 为utf8_bin或者utf8_general_cs,语句如下,注意mysql的版本号支不支持utf8_general_cs

  ALTER TABLE acc_user MODIFY COLUMN account VARCHAR(128)  COLLATE utf8_bin ;

由于我的mysql版本号不支持utf8_general_cs,所以这里就不作演示了

2. BINARY 这个也可以标识属性字段的大小写敏感性

    使用binary又有几种方式

    2.1 直接修改字段为binary

         ALTER TABLE acc_user MODIFY COLUMN account VARCHAR(128) BINARY ;

注意:ALTER TABLE acc_user MODIFY COLUMN account VARCHAR(128) BINARY ;的效果和ALTER TABLE acc_user MODIFY COLUMN account VARCHAR(128)  COLLATE utf8_bin ;的效果都是一样,查看acc_user的表结构:都是把account字段的COLLATE设置成 utf8_bin

2.2 在查询的时候,字段前面加上BINARY
SELECT * FROM acc_user WHERE BINARY account = "ZHANGsAN";

或者 ;SELECT * FROM acc_user WHERE BINARY(account) = "ZHANGsAN";

总结:

对于CHAR、VARCHAR和TEXT类型,BINARY属性可以为列分配该列字符集的 校对规则

BINARY属性是指定列字符集的二元 校对规则的简写。
排序和比较基于数值字符值。因此也就自然区分了大小写

    原文作者:EllisTian
    原文地址: https://blog.csdn.net/T2080305/article/details/82252535
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。