浅谈 MySQL 中的校对规则 (collation)

在我们开发过程中,最常见到的三种校对规则 (collation) 就是 utf8mb4_general_ciutf8mb4_unicode_ci,和 utf8mb4_bin。那么这三种排序规则之间有什么区别,在开发过程中又该怎么选择?这里就简单说一下我所了解到的知识,和我的理解。

校对规则不会导致乱码

之前听到一名同事说,“这个东西 (collation) 你再研究下,搞不好中文会乱码的”。显然这位同事没搞清楚字符编码 (encoding) 和校对规则 (collation) 的区别。

字符集是一套符号和编码,它实实在在决定了每个字符应当以怎样的规则被编码为二进制数据,以及在取出一系列二进制数据之后,又应当以怎样的规则还原为字符。比如我们喜闻乐见的 “锟斤拷” 就是因为 GBK 编码与 Unicode 编码之间转换出现问题导致的乱码。

而校对规则本身并不会参与字符的编码,所以它当然不会产生文字乱码的问题。当然,使用了不合适的校对规则,也会对数据产生一定的影响,具体有什么影响后面我们慢慢说。

校对规则是什么

校对规则是一套规范,它指明了数据在数据库中应当以哪种方式被比较和排序,包括排序的规则、是否大小写敏感,以及是否对重音标记敏感。

MySQL 中,校对规则通常按照字符集_语言或地区_字符敏感特性的规则来命名。

这三种校对规则有什么区别

首先,根据它们的后缀_ci 可以知道,这三种校对规则都是对大小写不敏感 (Case Insensitive) 的。

utf8mb4_general_ci 是对大小写和重音字符都不敏感的。比如,拉丁字符 ÀÁÅåāă是等同于字符 a 的。

utf8mb4_unicode_ci 是大小写不敏感,但是对重音字符敏感的。即,拉丁字符 Åå 是等同的,但是和 a 是不同的。

utf8mb4_bin 则是直接比较每个字符的 Unicode 码点 (code point)。

从效率上来讲,utf8mb4_bin > utf8mb4_general_ci > utf8mb4_unicode_ci

校对规则会产生什么影响

不同的排序规则,不仅对大小写和重音字符的处理不同,对于一些语言中特有的字符的处理也是不同的。

比如一个斯洛伐克人,他向数据库中插入了两条数据,其主键分别是 poistnýpoistny。但是因为他用了重音不敏感的 utf8mb4_general_ci,导致数据库判定这两个字符串是一样的,而抛出了重复主键的错误。[3]

另一个例子是,对于德语中的 ß,如果使用 utf8mb4_general_ci,那么它等同于 s,而如果使用 utf8mb4_unicode_ci,它则等同于 ss

所以,如果数据涉及的语言中包含有特殊的字符或者重音符号,而其比对结果的正确性又很重要时,那么就应当选择 unicode_ci 系列的校对规则,否则,general_ci 就够用了。当然,在必要的情况下,也可以选择这个语言对应的规则,比如 utf8_swedish_ci

对于中文来说,我在网上并没有找到相关的文章,同时根据自己的开发经验,general_ciunicode_ci 对于中文都没有什么明显的问题,所以两个都可以用。


  1. 1.Re: utf8_unicode_ci vs utf8_general_ci - MySQL Forums
  2. 2.MySQL collation charts
  3. 3. 斯洛伐克人的例子