MySQL案例-varchar的大小写字符比较

-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------

背景:
程序某日提出的SQL问题, 发现比较varchar字符串内容的时候, 大小写不敏感;
测试环境还原问题, 环境构造如下:

点击(此处)折叠或打开

1. create table case_sen(id int primary key, name varchar(32) not null);
2. insert into case_sen values(1,'LucyLoveLily');
   
3. insert into case_sen values(2,'lucylovelily');
   
4. insert into case_sen values(3,'TomHateLarry');
   
5. insert into case_sen values(4,'tomhatelarry');

效果如下:


实际上程序希望只有id=1的 匹配到,  
同样的问题也出现在like里面:


程序有问是不是 lower_case_table_names的问题, 显然......... 不是 _(:з」∠)_

场景:
MySQL-5.7进行的还原, 实际上这个问题 和版本没有关系;


分析:
首先可以确认的是, 表内的数据并没有问题, 该大写的还是大写, 该小写的还是小写;

那么问题最有可能出在MySQL的运算符"="和like上, 这两类运算的原理可能因为某些原因/设置/ 导致不进行 大小写区分;

排查 方向定下来以后, 试着用关键字comparison, case sensitive在文档里面找找, 发现有一个章节提到了这个问题,并给出了一些示例 ;
PS: 章节名   B.5.4.1 Case Sensitivity in String Searches

文档示例如图:


对文档的描述进行归纳:  非binary类型的string进行逻辑运算时, 会依据collate的配置来计算结果;

所以如果collate的设置对大小写不敏感, 那么就会出现测试环境中的效果;

那么问题来了, utf8mb4的默认collate是什么?


发现是utf8mb4_general_ci, 通过对文档内容的分析, 这个后缀ci代表的意思应该就是case- insensitive ;

那么试着换一下字符集的collate, 看看是不是能区分大小写;


Bingo~~~

当然, 在列上面指定collate为utf8mb4_bin也可以达到一样的目的;

PS: mysql的所有字符集默认都使用XXX_general_ci, 而有的字符集会有XXX_general_cs, 但是utf8是没有的, 所以要使用utf8_bin;

PPS: 兴趣阅读: https://www.percona.com/live/europe-amsterdam-2015/sites/default/files/slides/PL_AMS_Unicode_Booking169_v3.pdf

PPPPPPPS: 注意索引哟, 5.7是可以的, <5.7可没有试过 ╰(*°▽°*)╯

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29510932/viewspace-2134223/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29510932/viewspace-2134223/