mysql查询不区分大小写解决方案

mysql字符集和校对规则

有时在使用mysql时字符串的查询条件不管大小写都能查询到数据，例如：输入 aaa 或者aaA ,AAA都能查询同样的结果，说明查询条件对大小写不敏感。

大小写不敏感的根源在于字符集所使用的校对规则有关

1、查看mysql所有可用的字符集的命令是 

show character set

字符集是用来定义 MySQL 存储字符串的方式，校对规则则是定义了比较字符串的方式。字符集和校对规则是一对多的关系，MySQL 支持 30 多种字符集的 70 多种校对规则。

校对规则命名约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以_ci（case insensitive collation，不区分大小写）、_cs（case sensitive collation，区分大小写）或_bin（binary case sensitive collation，也就是说是区分大小写的，二元，即比较是基于字符编码的值而与language无关）结束。

2、服务器的字符集和校对规则

show variables like 'character_set_server';

 

show variables like 'collation_server';

可以看到当前服务器使用的字符集时utf8，该字符集对应的校对规则时utf8_general_ci，不区分大小写的。

3、数据库的字符集和校对规则

数据库的字符集和校对规则在创建数据库的时候指定，也可以在创建完数据库后通过“alter database”命令进行修改。需要注意的是，如果数据库里已经存在数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据的内容。

设置数据库字符集的规则是：

• 如果指定了字符集和校对规则，则使用指定的字符集和校对规则
• 如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则
• 如果没有指定字符集和校对规则，则使用服务器字符集和校对规则作为数据库的字符集和校对规则

推荐在创建数据库的时候明确指定字符集和校对规则，避免受到默认值的影响。

当前数据库的字符集和校对规则，可以使用 “ show variables like 'character_set_database '”和“show variables like ' collation_database '”命令

show variables like 'character_set_database';

show variables like 'collation_database';

4、表字符集和校对规则

表的字符集和校对规则在创建表的时候指定，可以通过 alter table 命令进行修改，同样，如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新的字符集进行存放。表的字段仍然使用原来的字符集。

设置表的字符集的规则和上面基本类似：

• 如果指定了字符集和校对规则，使用指定的字符集和校对规则
• 如果指定了字符集没有指定校对规则，使用指定字符集的默认校对规则
• 如果没有指定字符集和校对规则，使用数据库字符集和校对规则作为表的字符集和校对规则

推荐在创建表的时候明确指定字符集和校对规则，避免受到默认值的影响。

查看表的字符集和校对规则，可以使用 show create table 命令

解决mysql查询大小写不敏感

根据上面讲解的字符集和校对规则可知，查询时不区分大小写时由于设置的校对规则大小写不敏感导致的。

方法1、使用binary使大小写区分

select * from some_table where binary str='abc'
select * from some_table where binary str='ABC'

这样得到的查询结果就大小写敏感查询了。

同理

SELECT DISTINCT(BINARY name) AS Name FROM X;

 

方法2、建表时指定区分大小写的校对规则