[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目前在很多环境容易乱码,因此暂时这种情形下的解决方案显得不那么完美。

参考文档

浏览量(247) | 此条目发表在lamp专区, 计算机分类目录,贴了标签。将固定链接加入收藏夹。