最近遇到一个需求，需要查询关键词是否在词库中有相同的词（不区分大小写，比如movie 跟Movie 认为是相同的词）。如何实现这个问题呢？本文就讲一下这个问题。

mysql 版本：

5.6.19

表结构：本文中关键词使用name 字段。

CREATE TABLE `keyword` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `code` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '关键词id',
  `name` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '关键词',
  ...
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8851 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='关键词表'

总共有 741138 条记录。

1 lower 与 upper的用法与问题

查询某个词是否有相同的词（忽略大小写），首先想到的办法是将要查询的词转换成小写，然后再去查询。

步骤一、将要查询的词 在业务上先转换成小写。 
String keyword = "Aaa".toLower();

步骤二、使用MySQL的小写方法进行查询。语句如下。
SELECT name FROM keyword WHERE LOWER(`name`) = #{keyword}

这样业务上已经满足了，但是对索引执行lower 或者 upper函数之后，会使得索引失效。我们explain 之后发现每次查询大约100ms.

explain SELECT `name` FROM sz_keyword where LOWER(`name`) = "aaaa"

那么，有没有其他方案呢？就是下面要讲的，修改一下MySQL的校验规则 collate。

2 collate utf8_general_ci 忽略大小写

collate 是MySQL的校验规则，涉及到了大小写是否敏感。我们看下MySQL都支持哪些collate。

show collate;

还有好多，我们就不全截图了。

一般collate不指定，会使用默认的规则，在OSX系统中，一般是大小写敏感的。如果我们想设定大小不敏感，只要指定某个字段上为 xxxx_ci （ci 表示case insensitive）即可，当然也可以为某个表，甚至整个database 设定collate。

那我们尝试为name 字段修改校验规则。

alter table sz_keyword modify `name`  varchar(100) COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '关键词';

结果发现报错了：

这是因为name上面有unique 索引，之前是大小写敏感的，存入了很多数据，例如move 跟Move ，这是如果想改成大小写不敏感的，则这是不允许的。

那我们怎么做呢？两种方案：（1）去掉unique，改为普通索引，设定collate utf8_general_ci，然后使用“=” 查询即可。但是这样，之前精确查询的地方会有影响，都要排查一遍，看下是否影响？工作量不小。执行如下操作：

alter table sz_keyword drop index idx_name;

alter table sz_keyword add index idx_name (`name`);

alter table sz_keyword modify `name`  varchar(100) COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '关键词';

我们再来看执行select查询看下效果，以及explain？

SELECT `name` FROM sz_keyword where `name` = "movie Songs"

此时不用lower 已经可以查出多条结果了。

再看下explain

explain SELECT `name` FROM sz_keyword where `name` = "movie Songs"

type 已经是ref 了，不再是index. 查询数量也从738717 减少到3。

（2）保留unique 索引，将表里面所有的数据都整理一遍。将同字母但是大小写不同的一组词只取一个，其他删掉。然后设定collate utf8_general_ci，这样再使用这个字段进行忽略大小写查询即可。但是这种方案会涉及到洗数据。

除了上述两种方案，还有其他方案吗？下面再讲一种，function-base index. 简称FBI。

#3 function-base index

在Oracle 中支持 function-base index ，但是mysql 中不支持。在Oracle 中可以使用如下方式在function上建索引：

CREATE INDEX keyword 
    ON keyword (LOWER(name))

这样在查询的时候，使用 select name from keyword where LOWER(name) = 'AAA'，就能用上索引。

MySQL不支持这种用法怎么办呢？可以使用如下替代方案：

步骤一：新增一个字段，专门存储name 对应的小写字段。
ALTER TABLE keyword ADD COLUMN lower_name VARCHAR(255) AS (LOWER(name));
步骤二：在新增字段上建索引。
CREATE INDEX idx_lower_name ON keyword (lower_name);
步骤三：查询时，使用新建的索引字段查询。
select name from keyword where lower_name = 'aaa';

#4 总结本文结合一个实际的需求开发，整理了MySQL大小写问题，以及常见的三种方案。

#5 参考文献

COLLATE: dev.mysql.com/doc/refman/… dev.mysql.com/doc/refman/…

case sensitivity in mysql: federico-razzoli.com/case-sensit…

function-base index: use-the-index-luke.com/sql/where-c…

MySql 大小写总结

1 lower 与 upper的用法与问题

2 collate utf8_general_ci 忽略大小写