3招解决MySQL字符串取值大小写问题

数据库中字符串类型字段相关运算是否大小写敏感，这是个容易被设计者忽略的问题。MySQL数据库与其它许多流行的数据库在此问题上作出了不同的选择。若不了解这一点会导致严重的问题，本文对此给出了三种不同的解决方案。

30s

快速了解

MySQL数据库默认情况下，字符串字段的所有相关运算是大小写“不敏感”的。这一点与其它流行的数据库都不相同。

本文介绍了三种方法解决这个问题。

其中一种在查询时指定大小写敏感，但可能存在性能风险。

另外两种则是在表结构定义时定义。

MySQL

数据库备受争议的特性

先看一句SQL语句：

SELECT * FROM fruit WHERE name = 'Apple'

这是一句再简单不过的SQL语句，其目的就是从fruit数据表中查找name为“Apple”的那条记录。假设数据库中已经存在一条name字段值为“apple”的记录。没错，我没有拼写错误，就是全部小写的“apple”。请问上面这个语句能够查询出这条记录么？

正确的答案是，在MySQL数据库中你很可能可以查出记录。（如果你已经注意到“很可能”三个字，那么请你暂时放下，我会稍后解释。）也就是说MySQL数据库在执行查询时是大小写“不敏感”的。请问这是一件“好事”么？如果我们的查询条件换成id字段，你还期望大小写不敏感么？！

被震惊的你，一定禁不住想问：难道别的数据库也是这样么？我当初带着这个疑问分别在Oracle和PostgreSQL数据库中进行了测试。结果令人“心碎”，它们都是大小写“敏感”的。在这个问题上MySQL是如此“特立独行”。我又带着这个疑问到网上搜索了一番，结果却看到了大量的“吐槽”……

进一步的测试发现，在MySQL中不仅“=”运算大小写不敏感，所有的比较运算，以及LIKE运算皆是如此。

难道就没有办法大小写“敏感”么？MySQL作为最流行的数据库之一，当然不会。共有三种解决方案：

方法一：查询时指定大小写敏感。

方法二：定义表结构时指定字段大小写敏感。

方法三：修改排序规则（COLLATION）。

3个

解决办法

01

查询时指定大小写敏感

MySQL允许在查询的时候指定以大小写“敏感”方式，需要使用关键字“BINARY”，改写上述查询如下：

SELECT * FROM fruit WHERE BINARY name = 'Apple'

或者

SELECT * FROM fruit WHERE name = BINARY 'Apple'

其它运算也是如此，例如：

SELECT * FROM fruit WHERE BINARY name LIKE 'App%'

rumba-commons-jdbc.jar针对这个问题也提供了相应的解决方案。对于使用SQL对象模型构造SQL语句的用户，可以使用以“CaseSensitive”后缀的方法达到上述同样的效果。例如：

SelectStatement select = new SelectBuilder() // .from("fruit") // .where(Predicates.equalsCaseSensitive("name", "Apple")) // .build();

很多时候当发现MySQL数据库存在上述问题时，系统已经运行了一段时间，如果采用方法二或方法三的代价可能会很大。使用此方法最大的好处便是可以快速实现功能。

但是这个方法也存在很大的限制：如此可能因为无法使用索引导致查询性能下降。原因很好理解，因为此时针对查询字段的索引也是按照大小写不敏感方式建立的。除非数据量不大，或者在你的应用中不在乎这点性能上的损失，那么只能选择方法二或方法三了。

02

定义表结构时指定字段大小写敏感

以下代码在创建fruit表时指定其中的name字段大小写“敏感”：

CREATE TABLE fruit (

...

name VARCHAR(64) BINARY NOT NULL,

...

)

关键字“BINARY”指定name字段大小写敏感。如此在查询时就算不使用“BINARY”关键字，以下查询语句也是大小写敏感的：

SELECT * FROM fruit WHERE name = 'Apple'

在此基础上创建的name相关的索引也是大小写敏感的，也就能够使用索引来提高性能。MySQL允许在大多数字符串类型上使用BINARY关键字，用于指明所有针对该字段的运算是大小写敏感的，更多信息请参见MySQL官方文档。

这种方法使得设计者可以精确地控制每个字段是否大小写敏感。不过在很多系统的设计中，期望大部分甚至所有的字段统一大小写敏感。MySQL也提供了解决方案，这就要用到方法三。

03

修改排序规则（COLLATION）

涉及字符串的各种运算其核心必然涉及到采用何种字符排序规则（Collation，也有翻译为“核对”）。本质上MySQL是通过Collation取值决定字符串运算是否大小写敏感。

“utf8_general_ci”是一个具体的Collation取值。每个具体的Collation都对应唯一的字符集，可以看出该Collation对应字符集为“utf8”。而与大小写敏感问题相关的是其后缀“_ci”，MySQL官方文档对其的解释是“Case Ignore”的缩写，即大小写不敏感。由于MySQL将“utf8_general_ci”指定作为字符集utf8的默认Collation，这也就导致文章开头所说的现象。与此同时，MySQL也提供了其它的Collation取值选项，“utf8_bin”就是大小写敏感的。事实上所有大小写敏感的Collation都以“_bin”或“_cs”为后缀，前者是“Binary”的缩写，后者是“Case Sensitive”的缩写。

MySQL数据库允许在“库”、“表”和“列”三个级别上指定Collation。当同时指定时，优先关系是：列>表>库。

以下代码指定了数据表的默认排序规则，意味着整个数据表中所有字符串类型字段的默认Collation：

CREATE TABLE fruit (

...

name VARCHAR(64) NOT NULL,

...

) COLLATE = utf8_bin;

需要额外说明的是，由于每个Collation都对应唯一的字符集，因此上述代码由于指定Collation，就没有必要再使用“CHARACTER SET = ”指定采用的字符集了。

以下代码在“列”上指定Collation：

CREATE TABLE fruit (

...

name VARCHAR(64) COLLATE utf8_bin NOT NULL,

...

);

以下代码在“库”上指定Collation：

CREATE DATABASE mydb COLLATE utf8_bin;

方法三与方法二都是在表结构定义时指定是否大小写敏感，因此都能有效避免方法一由于无法利用索引导致查询性能下降的问题。方法二的优点是简单易学，但是缺少“表”以及“库”级别的选项，适合“多数不敏感少数敏感”的情况。方法三则更加灵活，更加适合“多数敏感少数不敏感”的情况，但要求使用者必须了解Collation相关的知识。比如Collation有哪些合法取值。可以通过以下语句查询到所有的Collation：

SELECT * FROM information_schema.COLLATIONS;

以上，你get了吗？

无

海鼎Fun

有料，有爱，有梦想

空·