浅谈MySQL中的大小写敏感问题
发布于 5 年前 作者 blackmatch 4353 次浏览 来自 分享

前言

MySQL中的大小写敏感问题,可以从服务器(Server)、数据库(Database)、表(Table)、字段(Column)这4个级别来配置,MySQL的很多配置、操作等都是基于这4个级别的。这4个级别的优先级为:字段>表>数据库>服务器。而我们最关注的大小写敏感问题通常是对于数据而言的,举个简单的例子:字符串book和字符串Book存入到数据库后,我们通过SQL语句查询的时候,如果这两个字符串是相等的,那么说明MySQL比较这两个字符串的时候采用了大小写不敏感的方式,反之,则是使用了大小写敏感的方式。在讨论MySQL大小写敏感问题之前,我们需要先了解MySQL的两个概念:字符集(CHARACTER SET)和校对规则(Collation)。

字符集(CHARACTER SET)和校对规则(Collation)

字符集的通常解释是:符号和编码的集合。举个例子:假设我们有4个字母:A,B,a,b。我们给每个字母编个号码:A=0,B=1,a=3,b=4。我们就可以说字母A是符号,编号0是它的编码。所有的字母和它们的编码组合起来就是我们通常说的字符集。如果我们想要比较两个字符串A和B的值的大小,那么我们很容就想到他们的编码,A的编码是0,B的编码是1,因为0小于1,所以字符串A小于字符串B。我们做的这个比较就是给这个字符集(CHARACTER SET)应用了一套校对规则(Collation)

MySQL拥有一套完整的字符集和校对规则。每一种字符集至少包含一种校对规则,且每种字符集都有默认的校对规则。

  • 可以使用SHOW CHARACTER SET语句查看MySQL支持的字符集列表:

show_character_set.png

  • 可以使用SHOW COLLATION语句查看MySQL支持的校对规则:

show_collation.png

大小写敏感问题

接着上面的例子,如果我们想要大写字母A和小写字母a是相等的呢?那么我们需要做的就是将大写字母A和小写字母a的编码设置为一致的,然后比较它们的编码即可。这就是我们通常说的大小写不敏感校对规则。反之,如果将大写字母A和小写字母a视为不相等的,就是我们通常说的大小写敏感校对规则

在不指定字符集和校对规则的情况下,MySQL会使用默认的字符集(utf8)和校对规则(utf8_general_ci)。校对规则的名称遵循规则:以其相关的字符集开头,后加上一个或者多个后缀用于区分不同的校对规则。相关后缀说明:

后缀 含义
_ai 口音不敏感(Accent insensitive)
_as 口音敏感(Accent sensitive)
_ci 大小写不敏感(Case insensitive)
_cs 大小写敏感(case-sensitive)
_ks 假名敏感(Kana sensitive)
_bin 二进制(Binary,大小写敏感)

由此可以知道:默认情况下,MySQL所使用的校对规则是大小写不敏感的

设置大小写敏感

在日常的开发场景中,可能很少遇到需要设置大小写敏感的场景,而我恰巧遇到了。下面举例说明:

  • 先创建一张用户表:
CREATE TABLE `users` (
  `username` varchar(32) NOT NULL,
  `nickname` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这里我们先使用默认的字符集和校对规则。

  • 插入两条数据:
INSERT INTO `cs_test`.`users` (`username`, `nickname`) VALUES ('user1', 'black');
INSERT INTO `cs_test`.`users` (`username`, `nickname`) VALUES ('user2', 'match');
  • 查询数据:
SELECT * FROM users WHERE username='user1';
SELECT * FROM users WHERE username='usER1';

这两条SQL语句的查询结果是一致的:

query_user.png

  • 现在我需要向users表中插入一条usernameuseR1另外一个用户,发现报错了:
INSERT INTO `cs_test`.`users` (`username`, `nickname`) VALUES ('useR1', 'whatever');

insert_error.png

理由很简单:因为MySQL此时使用的是大小写不敏感的校对规则,所以user1useR1是相等的,而usernameusers表的主键,所以在插入数据时会报主键冲突的错误。

  • 设置users表的username字段使用大小写敏感的校对规则:
ALTER TABLE `cs_test`.`users` 
CHANGE COLUMN `username` `username` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ;

然后再次尝试插入数据,这次插入成功了。此时users表中的数据如下:

users.png

  • 再次查询用户表:

query_user_again_1.png

query_user_again_2.png

query_user_again_3.png

发现这次users表的username字段已经彻底变成了大小写敏感了。

总结

  • 本文讨论的是MySQL数据校对规则的大小写敏感问题,而不是讨论表名的大小写敏感问题(网上很多文章都是讨论表名的大小写敏感问题)。
  • 对MySQL的字符集和校对规则有一定的了解后有助于理解大小写敏感问题。
  • MySQL的utf8字符集并没有utf8_general_cs校对规则,网上有些文章存在误导。
  • MySQL的utf8_bin校对规则是大小写敏感的。
  • MySQL的4个级别优先级为:字段(Column)>表(Table)>数据库(Database)>服务器(Server),进行相关配置时尤其要注意。
  • 最好不要对主键等其他含有索引的字段设置大小写敏感,容易导致索引失效,从业务角度考虑也不太合理。
回到顶部