mysql 字符类型字段查询大小写不区分问题的解决方式
前言:在实际的项目开发中,我们可能会遇到下面的问题,如下图所示,查询的时候,我传入的是"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属性是指定列字符集的二元 校对规则的简写。
排序和比较基于数值字符值。因此也就自然区分了大小写