[Galois21原创]MySql大小写不敏感及模糊查询问题研究

发现网上对mysql查询问题的解析过于表面或者一知半解，因此这里做详细说明，试图彻底解开这个谜团。

MYSQL查询中容易遇到两个常见问题：

一、大小写不敏感。使用select * from table_name where field_name=’baidu’搜“baidu”，结果中把“BAIDU”记录也带出来了。

二、模糊搜索出现谬误。使用select * from table_name where field_name like ‘%F%搜”了”，一些不含F，也不含f，却含有“你”这种的字眼的记录也带出来了。

这两个问题看起来是不同问题，放在一起说，是因为很有相似之处，个别方案一次性可以解决以上两个问题。怎么讲呢？

问题原因剖析

1、搜索功能的大小写不敏感是mysql自古就有的。问题一就好理解了，因为是mysql的问题。

2、由于unicode是将每一个文字转成唯一的英文编码，UTF8是unicode的一种。当mysql数据库默认字符集是UTF8的时候，“你”字（UTF8：你）在mysql中以UTF8形式存在，而恰好这个里含有F字母。因此用“%F%”模糊搜索“F”字母，就会搜索到“你”相关记录，于是就搜错了。问题二就解决了，但请注意这造成的现象，精确搜索不会出现该问题，只有模糊搜索会，读者可以自己理解下。

解决方案

1、将查询错的字段属性修改为binary。[解决模糊搜索bug、大小写敏感]

解决方法：将你要觉得搜错的那个字段在数据库中更改字段属性为binary属性（二进制属性），username varchar(30) BINARY NOT NULL default ”, 如果表已经建好了，使用：alter table usertest modify username varchar(32) binary; 来就改表的属性。（方式很多种，可自行谷歌）。

解决原理：虽然mysq默认字符集设置UTF8一般都会导致这种问题，但如果将对应字段数据转换成为二进制（0101的机器码），那么mysql底层搜索就会强制将搜索词“F”转成对应的二进制码搜索，因为已经转到数字匹配层面，而不是UTF8的英文匹配层面，避免了英文匹配引起bug的问题，就可以解决了。

效果：对应英文字母的准确查询，将严格区分大小写。同时模糊搜索严格获得搜索结果。

短板：无法解决希望“对大小写不敏感+不出现模糊查询bug” 的双重需求

2、修改MYSQL默认字符集为GBK。[解决模糊搜索bug，大小写不敏感]

解决方法：ini中可以修改默认字符集（my.ini的default-character-set属性设为gbk）；或者可以php中代码控制；linux下用源码安装方式的话，也可以在安装的时候选择 –with-charset=gbk（如shell命令：./configure –prefix=/usr/local/mysql –with-charset=gbk）。（方式很多种，可自行谷歌）

解决原理：前面说到，模糊查询错误跟UTF8这编码问题有很大关系。而GBK字符集则不同，它不走英文字母编码逻辑，采用GBK的mysql数据必要所以不会出现问题。

效果：模糊搜索严格获得搜索结果，但无法解决大小写不敏感问题。

短板：对环境改动大，gbk很多地方容易乱码；无法解决大小写不敏感问题。

3、 locate() 函数方案[不完美实现大小写敏感]

解决方法：使用 Mysql 的 locate() 函数来判断。例如: SELECT * FROM table_name WHERE locate(field_name,’F’) > 0; 、

解决原理：由于mysql中locate()这个函数是多字节安全的。在 MySQL 3.23 中，这个函数是字母大小写敏感的，当在 MySQL 4.0 中时，如有任一参数是一个二进制字符串，它才是字母大小写敏感的。 因此，这个查询避免了大小写敏感问题。LOCATE(substr,str,pos) 返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果 substr 不在 str 中返回 0 。
效果：使大小写敏感了，但只能针对查询一条记录的情形。

短板：由于locate()只是返回第一次出现的位置，所以只能查询满足条件的第一条记录，连续查询需要加循环语句辅助实现。

4、select语句中加BINARY[解决模糊搜索bug、大小写敏感]

解决方法：如将SQL语句改为 SELECT * FROM table_name WHERE field_name LIKE BINARY ‘%了%’;

解决原理：BINARY 操作符将跟在它后面的字符串强制作为一个二进制字符串。这可以很容易地强制一个列的比较以字母大小写敏感方式进行，即使该列没有定义为 BINARY 或 BLOB。本质和方法1类似。

效果：对应英文字母的准确查询，将严格区分大小写。同时模糊搜索严格获得搜索结果。

短板：无法解决希望“对大小写不敏感+不出现模糊查询bug” 的双重需求

方案总结和评估：模糊搜索错误的问题必然是要解决，因此1、2、4中必需其一，以下是常用需求情形和对于策略：

【解决模糊搜索bug、大小写敏感】：1或者4方案均可。建议方案1，简单易行，不用改太多代码。

【解决模糊搜索bug、大小写不敏感】：2方案即可。这个比较尴尬地方是，GBK目前在很多环境容易乱码，因此暂时这种情形下的解决方案显得不那么完美。

参考文档