首页 > 代码库 > mysql大小写敏感与校对规则
mysql大小写敏感与校对规则
大家在使用mysql过程中，可能会遇到类似一下的问题：
root@chuck 07:42:00>select * from test where c1 like ‘ab%‘;
+-----+
| c1 |
+-----+
| abc |
| ABD |
+-----+
模糊匹配 ab%，结果以AB开头的字符串也出现在结果集中，大家很自然的认为是大小写敏感的问题。那么mysql中大小写敏感是如何控制的；数据库名，表名，字段名这些字典对象以及字段值的大小敏感是如何控制的；以及校验规则与索引的关系，这是本文要讨论的内容。
mysql中控制数据库名和表名的大小写敏感由参数lower_case_table_names控制，为0时表示区分大小写，为1时，表示将名字转化为小写后存储，不区分大小写。字段名通常都是不区分大小写的，字段值呢？字段值的大小写由mysql的校对规则来控制。提到校对规则，就不得不说字符集。字符集是一套符号和编码，校对规则是在字符集内用于比较字符的一套规则，比如定义‘A‘<‘B‘这样的关系的规则。不同的字符集有多种校对规则，一般而言，校对规则以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束 。比如 utf8字符集，utf8_general_ci,表示不区分大小写，这个是utf8字符集默认的校对规则；utf8_general_cs表示区分大小写，utf8_bin表示二进制比较，同样也区分大小写。
校对规则通过关键字collate指定，比如创建数据库d1，指定字符集为utf8，校对规则为utf8_bin
CREATE DATABASE d1 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
通过上述语句说明数据库d1中的数据按utf8编码，并且是对大小写敏感的。有时候我们建库时，没有指定校对规则校对时字符大小写敏感，但是我们查询时，又需要对字符比较大小写敏感，就比如开篇中的例子，只想要ab打头的字符串。没关系，mysql提供了collate语法，通过指定utf8_bin校对规则即可。
root@chuck 08:19:35>select * from test where c1 like ‘ab%‘ collate utf8_bin;
+-----+
| c1 |
+-----+
| abc|
+-----+
这里还有另外一种方法，通过binary关键字，将串转为二进制进行比较，由于大小写字符的二进制肯定不同，因此可以认为是区分大小的一种方式。
root@chuck 07:50:35>select * from test where binary c1 like ‘ab%‘;
+-----+
| c1 |
+-----+
| abc |
+-----+
最后要说明一点的是校对规则与索引存储的关系。因为校对规则会用于字符串之间比较，而索引是基于比较有序排列的，因此校对规则会影响记录的索引顺序。下面举一个小例子说明：
1 | 建表 | create table test(c1 varchar(100), primary key(c1));
| create table test2(c1 varchar(100), primary key(c1)) collate utf8_bin;
|
2 | 初始化数据 | insert into test(c1) values(‘abc‘); insert into test(c1) values(‘ABD‘); insert into test(c1) values(‘ZBC‘); | |
3 | 查询 | select * from test; | select * from test2; |
4 | 返回 结果集 | | abc | | | ABD | |
表1
从表1可以看到test和test2返回的结果集中，记录的相对顺序是不同的，因为是全表扫描，返回的记录体现了主键顺序。由于test表校验规则采用默认的utf8_general_ci，大小写不敏感，因此abc<ABC<ZBC；同理，test2采用utf8_bin，大小写敏感，因此ABD<ZBC<abc。
mysql大小写敏感与校对规则