mysql 大小写 哪种好_MySql 大小写总结

最近遇到一个需求，需要查询关键词是否在词库中有相同的词(不区分大小写，比如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 参考文献