码农pilot的个人博客

0%

浅谈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.斯洛伐克人的例子