MySQL数据记录大小写问题

前段时间在项目中，发生了某些修改操作无法正常完成，追溯其原因，发现由大小写不敏感引起的。下面就和大家分享一下~~

问题示例

表名称：test

字段名称：name，类型：varchar

字符集：utf-8

值：aaa、AAA

通过小写值查询：

结果：查询结果可以看出其对于大小写是不敏感的，如若对于需要大小写敏感的业务来说，无疑是件麻烦事。

解决方法

修改排序规则

MySQL中字段值的大小写是由collation来控制的。那collation是什么呢？

提到collation，就不得不提及字符集。字符集大家都不陌生，是多个字符（各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等）的集合，字符集种类较多，每个字符集包含的字符个数不同，常见字符集：ASCII字符集、GB2312字符集、Unicode字符集等。而我们经常用的UTF-8是一种针对Unicode的可变长度字符编码，又称万国码。在MySQL中，字符集的概念和编码方案被看作是同义词，一个字符集是一个转换表和一个编码方案的组合。

排序规则（collation）是在字符集内用于比较字符的一套规则，比如定义'A'

MySQL可以使用对种字符集和排序规则来组织字符。一般而言，collation以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束 。

譬如utf8字符集：

Ø utf8_general_ci：不区分大小写，这个是utf8字符集默认的collation；

Ø utf8_general_cs：区分大小写；（不过在我安装的mysql中没查到utf8_general_cs，查看命令mysql> show collation）；

Ø utf8_bin：二进制比较，同样也区分大小写。

在了解collation后，再去查看“test”表的字符集和collation：

果然，name字段，字符集为utf8，排序规则为utf8_general_ci。现将其排序规则改为utf8_bin后，再去使用同样条件查询，结果如下：

表信息：

通过小写值：

“AAA”未出现，大小写敏感了。

修改字段类型

MySQL类型当中，字符串可用四种类型来存储：char、varchar、binary、varbinary，其中char与varchar保存字符数据，而binary与varbinary保存二进制字符串，没有字符集限制，排序是基于字节的数值进行的。

Ø char：使用固定长度的空间存储非二进制字符串。插入时，对于少于设置长度时会自动在尾部加空格，查询时，尾部的空格就会被丢弃掉；

Ø varchar：保存可变长度的非二进制字符串，需要使用额外的一个或两个字节存储字符串长度；

Ø binary ：使用固定长度保存二进制字符串。插入时，对于少于设置长度时会自动在尾部加’\0’，查询时，需要加入’\0’,譬如长度为5，写入值为aaa，查询值为‘aaa\0\0’；

varbinary：保存可变长度的二进制字符串，实际占用的空间为实际存储字节长度加一；

其中需要注意的是，binary与varbinary的最大长度为字节长度，而char和varchar对应的是字符长度。

由此可以判断，可将varchar类型转为varbinary，则可以达到区分大小写的效果，结果如下：

表信息：

通过小写值：

修改条件语句

如果在创建数据库表时没有指定是否大小写敏感时，可通过语句级的collate和binary实现。

表信息：

通过小写值：

指定 collation：

通过binary把字符串转化为二进制比较：

以上为解决数据大小写问题的三种方式：修改排序规则、修改字段类型、修改条件语句。可依据具体情况来选择不同方式。当然最好在设计表时，依据功能需求，考虑是否需要字段大小写敏感，减少后续要进行修改的复杂度。如果必须要进行修改，比较对已有表与数据的影响力，个人比较偏向于在条件语句中指定collate 或binary，而不建议修改数据类型或排序规则。

Qtest是360旗下的专业测试团队！

是WEB平台部测试技术平台化、效率化的先锋力量！