1. 引言

在你使用MySQL的时候，有没有遇到过字符串出现乱码的情况？想查一个英文字符串结果查出来了特殊字符？字符串排序有时小写在前有时大写在前？如果在使用MySQL时不在意字符集、Collation等相关配置，那么就可能会碰到这些问题，本文将以几个例子作为开篇。

1.1 例1

一个客户端的错误配置，可能导致字符串二进制存储错乱，并且导致其他正常客户端查询出现乱码，如下：

1.2 例2

用等值条件查询字符串列时，多返回了一个完全不同的字符串，难道MySQL出Bug了？其实不是的，字符串的比较和Collation配置息息相关，稍不注意就可能得到令人费解的结果。

1.3 例3

下面这个例子展现了Collation对于字符串排序的影响，怎么有时候是小写优先，有时候是大写优先，不同字符串的先后顺序是怎么确定的？这都是配置的Collation决定的。

通过上面3个例子可以看出，我们在MySQL中使用字符串时，字符集和Collation是非常重要的配置项，一旦配置错误就可能无法按照预期进行使用。当我们在使用MySQL查看表定义时，经常可以看到如下例所示的CHARSET、COLLATE等字眼，所以相信大家对于这些字眼并不陌生。然而但很多时候我们并不清楚这些字符集、Collation的具体含义，也不知道究竟如何配置才好，要么跟着默认配置走，要么从已有库表的定义那里copy过来，但是这些“祖传配置”真的适合当前的应用么？合理地选择字符集、Collation、了解字符串如何比较将能很大程度上帮我们避免前文例子中所描述的问题，因此本文就将对此展开介绍，希望能对你有帮助。

MySQL8.0本身支持很多种字符集(Character Set)与Collation，本身的配置有时候令用户眼花缭乱，本文就将围绕它们由浅入深进行介绍，希望能够回答以下几个问题：

• • 字符集(Charset)和Collation是什么？
• • 如何配置不同层次的字符集和Collation？它们的影响范围有多大？
• • 最常用的Unicode字符集是如何存储、比较字符串的？
• • Binary字符集和非二进制字符集的_bin Collation该如何选择？

本文将分为两部分进行介绍，组织结构如下：

第一部分(概念介绍与MySQL的配置项)

• • 引言
• • 字符集(Charset)和Collation是什么？
• • 查看MySQL支持的Charset和Collation
• • Unicode与UTF-8
• • 配置Charset和Collation

第二部分(字符集转换与Unicode、Binary字符集的排序算法)

• • 转换Charset
• • Unicode字符串排序算法
• • binary Charset与_bin Collation
• • 总结

2. 字符集(Charset)和Collation是什么？

字符集(Character Set)是一组符号和编码。Collation是一组用于比较字符集中字符的规则。让我们以MySQL-8.0默认的utf8mb4字符集和utf8mb4_0900_ai_ci Collation为例，明确这个区别。下表是六个字符在utf8mb4字符集中的编码，utf8mb4字符集允许字符的编码是非定长的，长度可以是1～4 bytes，具体的编码方式将在“Unicode与UTF-8”章节介绍。

对于上面6个字符，如果我们仅看二进制编码对应的数值大小进行比较那么它们的大小关系就是“ECollation，在MySQL中以“_bin”结尾。但是很多时候，我们希望不论大小写，a都应该排在e和E前面，对于欧洲一些语言或者中文拼音来说，不论字母上面有没有音调符号，a也应该排在e前面，于是更加精细的Collation就应运而生，它包含了很多契合人类语言习惯的规则定义。比如上面例子中6个字符在utf8mb4_0900_ai_ci Collation看来，它们的大小关系是“a=ắ=𝔸Collation里，所有字符都忽略了音调、大小写再进行比较，这就比二进制Collation复杂了一些。

上面只是用utf8mb4字符集中的6个字符举了个例子，实际上其内几乎包含了世界上各个语言的文字，这些字符之间的定制化比较规则将更加复杂，不光是音调、大小写，有时还会有多字符映射(例如utf8mb4_0900_ai_ci中 ß = ss)。为此Unicode专门定义了字符串比较算法，解释了如何进行字符的比较，MySQL也是据此实现的自己的比较算法，这一部分将在“Unicode字符串比较算法”详细介绍。

从上面的描述我们可以看出，字符集是符号到编码一一映射的集合，一组Collation是对字符进行比较的一系列规则，一个字符集上可以有很多组Collation。为方便描述，后文使用Charset代表字符集。

3. 查看MySQL支持的Charset和Collation

3.1 查看Charset

MySQL支持了很多种Charset^[1]以及Collation，可以使用如下语句查看支持的Charset，其中Charset列代表字符集的名称、Description列代表描述、Default collation列代表该字符集的默认Collation、Maxlen列代表该字符集中最长编码的字节数：

3.2 查看Collation

MySQL-8.0默认使用的Charset是utf8mb4，这里以它为例使用如下语句查看该字符集可以使用的Collation，其中Compiled列代表该Collation是否在MySQL源码中实现：

在MySQL中，Charset和Collation遵循以下规则：

• 同一个Collation不能被多个Charset使用，也就是Collation依附于某一个Charset；
• 每个Charset都有一个默认Collation；

3.3 Collation的Pad_attribute

查看Collation信息时可以发现有一个Pad_attribute列，其值为“NO PAD”或“PAD SPACE”，这定义了Collation对待字符串尾部空格的态度。MySQL中大部分Collation该属性为“PAD SPACE”，基于UCA 9.0.0(名称中带0900字样)实现的Collation该属性为“NO PAD”：

• • PAD SPACE：当比较(不包括LIKE)两个字符串的时候，尾部空格将被忽略，例如认为"a"与"a "相等；
• • NO PAD：当比较两个字符串的时候，尾部空格不能忽略，例如认为"a"与"a "不等；
• • 使用LIKE操作符时，不受该值影响，尾部空格不能忽略；

3.4 Collation的命名规则

Collation名称以其关联的Charset名称开头，通常后面跟着一个或多个后缀，表示其他特征。例如，utf8mb4_0900_ai_ci和latin1_swedish_ci分别是utf8mb4和latin1的Collation。Binary Charset只有一个Collation，也命名为binary，没有后缀。

对于特定语言的Collation包括一个区域代码或语言名称。例如，utf8mb4_tr_0900_ai_ci和utf8mb4_hu_0900_ai_ci中的_tr和_hu分别代表使用土耳其语和匈牙利语的规则对utf8mb4中的字符进行排序。

Collation后缀表示是否区分大小写、音调、平(片)假名，或者是二进制的，下表显示了用于表示这些特征的后缀。如果Collation名称中不包含_ai或_as，则名称中的_ci隐喻着_ai(例如utf8mb4_unicode_ci既不区分音调也不区分大小写)，名称中的_cs隐喻着_as(例如latin1_general_cs既区分音调也区分大小写)：

Unicode字符集的Collation名称可能包括版本号，以表示Collation基于哪个版本的Unicode排序算法(UCA，后文会详细介绍)。名称中不含版本号的Collation则默认基于4.0.0版本的UCA。例如：

• • utf8mb4_0900_ai_ci基于UCA 9.0.0^[2]
• • utf8mb4_unicode_520_ci基于UCA 5.2.0^[3]
• • utf8mb4_unicode_ci基于UCA 4.0.0^[4]

4. Unicode与UTF-8

为了便于阅读后文，这里先介绍一下Unicode^[5]标准和UTF-8^[6]编码。

4.1 Unicode标准

Unicode标准由非盈利组织Unicode联盟^[7]维护，致力于整理和编码世界上大部分文字系统。Unicode标准当前最新版为2022年9月发布的15.0.0版本，收录超过14万字符，每个字符都被分配了独一无二的码点(数值编号)。Unicode已经成为ISO国际标准的一部分，最常见的Unicode编码格式有与ASCII兼容的UTF-8和与UCS-2兼容的UTF-16。

Unicode标准将编码空间划分为17个平面，编号从0到16，其中第0平面称为基本多文种平面(BMP，U+0000到U+FFFF)，而第1到16平面被称为辅助平面(U+10000到U+10FFFF)，这些平面与BMP平面一起至少需要21bit的编码空间，略少于3个字节。BMP平面的码点可以使用单个UTF-16编码单位(2字节)表示，或者使用1～3个字节的UTF-8进行编码；辅助平面的码点在UTF-8中使用4个字节进行编码，在UTF-16中使用4个字节进行编码。

4.2 UTF-8编码

出于节省空间等目的，实际上对Unicode标准进行编码有不同实现方式，Unicode的实现方式被称为Unicode转换格式(Unicode Transformation Format，简称为UTF)，最常见的当属UTF-8编码，其他实现方式还包括UTF-16(字符用两个字节或四个字节表示)和UTF-32(字符用四个字节表示)等，下面给出一些示例：

可以看出Unicode给字符分配的原始码点(编号)和UTF-8具体编码还是有很大不同的，这是因为UTF-8编码以8bit(1字节)为单位对Unicode字符进行变长编码，具体转换规则如下表所示(单元格内首行为16进制表示，次行为2进制表示)：

可以看出转换规则其实很直观，就是把Unicode码点对应的数值用3个字节存下来(最多用21个bit)，然后根据自己所处的范围将bit位依次填入UTF-8对应空位即可。

汉字的码点空间^[8]如下。utf16对于BMP平面字符(在下表中Unicode码点仅需两字节的部分)会使用2字节编码，而utf8对于BMP平面的汉字需要使用3字节编码，非BMP平面的汉字utf16和utf8都需要4字节编码。所以如果存储字符基本都是汉字时，utf16字符集的编码长度始终优于或等于utf8字符集的编码长度，可以有效帮助减少存储空间，最多减少1/3的存储空间。

4.3 MySQL内支持Unicode标准的Charset

MySQL支持多种Unicode Charset：

• • utf8mb4：使用一到四个字节表示每个字符的Unicode字符集的UTF-8编码。
• • utf8mb3：使用一到三个字节表示每个字符的Unicode字符集的UTF-8编码，仅支持表示基本多文种平面(BMP)。在MySQL 8.0中，这个字符集已被deprecated，应该尽快改用utf8mb4。
• • utf8：utf8mb3的别名。在MySQL 8.0中，这个别名已被deprecated，应改用utf8mb4。预计在未来的版本中，utf8将成为utf8mb4的别名。
• • ucs2：使用两个字节表示每个字符的UCS-2编码，仅支持表示基本多文种平面(BMP)。在MySQL 8.0.28中已被deprecated，预计在未来的版本中将被移除。
• • utf16：使用两个或四个字节表示每个字符的UTF-16编码，类似于ucs2，但包含了对补充辅助平面的扩展。
• • utf16le：类似于utf16，但是小端序而不是大端序。
• • utf32：使用四个字节表示每个字符的UTF-32编码。

5. 配置Charset和Collation

5.1 系统变量

使用show variables命令可以很方便地查看相关变量：

MySQL之所以有这么多和Charset相关的变量，是因为这些变量将在不同维度上起作用。这些系统变量中最重要的是跟连接相关的几个变量：character_set_client、character_set_results、character_set_connection、collation_connection，将在后面“配置连接”小节中详细介绍。剩余几个变量的含义如下：

• character_set_server和collation_server：如果在CREATE DATABASE语句中没有指定DATABASE的Charset和Collation，则该DATABASE默认Charset和Collation就是character_set_server和collation_server，它们没有其他用途。
• character_set_database和collation_database：该组变量其实是用于展现当前所在DATABASE(通过use db_name切换)的默认Charset和Collation，由数据库本身进行设置，切换DATABASE时这两个变量会跟着变化。当没有use某个DATABASE的时候，这一对变量和character_set_server、collation_server的值相同。因此可以发现该组变量其实是用于展示信息的，虽然还是可以被用户修改，但不建议使用，从8.0.14开始只有有权限的用户才能修改，未来版本会变成read only的变量。
• character_set_filesystem：此变量用于涉及文件路径的字符串字面量，例如在LOAD DATA和SELECT ... INTO OUTFILE语句以及LOAD_FILE()函数中。这类文件名会在尝试打开文件之前，从character_set_client转换为character_set_filesystem。默认值是binary，这意味着不进行转换。如果文件系统使用某种编码例如UTF-8表示文件名，则应将character_set_filesystem设置为utf8mb4。
• character_set_system：此变量是Global read only变量，设置为utf8mb3，所有元数据使用此Charset，元数据包括列名、database名、用户名、版本名以及SHOW命令的大部分字符串结果。使用utf8mb3存储元数据并不意味着服务器以character_set_system字符集返回列名，当执行“SELECT column1 FROM t”时，列名“column1”本身是转换为character_set_results系统变量确定的Charset，再从服务器返回到客户端的。下面这个例子尝试在表名中包含非utf8mb3字符，可以看出是不成功的。非法字符会被转换成'?'进行存储。注：由于显示问题，下列SQL中的可能会被显示成其乱码。

5.2 配置连接

在真正介绍character_set_client、character_set_results、character_set_connection、collation_connection这四个变量之前，先介绍一下字符串字面量的概念，如下面最简单的一个例子，'string'这个字符串就是字符串字面量，也就是客户端发来的语句里面的字符串，所有字符串字面量都有自己的Charset和Collation，可以显式指定(具体方法后续小节详细介绍，这里先有个概念)。character_set_connection和collation_connection就是字符串字面量的默认Charset和Collation。

一个例子

下面用一个比较极端的例子来介绍一下这几个变量是如何起作用的。假设客户端使用utf8mb4字符集，并且在建立连接时将本Session的character_set_client和character_set_results设置成了utf8mb4；由于想让字符串字面量使用latin1字符集的Collation，所以又将character_set_connection和collation_connection分别设置成了latin1和latin1_swedish_ci；而查询语句访问到的列使用的Charset和Collation又分别是ucs2和ucs2_general_ci。那么整个过程将会根据这些变量的设置发生多次字符集转换，如下图所示：

下面文字描述一下上图中的各个步骤：

• 查询语句中的字符串字面量'á'最开始在客户端的编码使用的是utf8mb4字符集，编码为0xC3A1
• 服务端接收后会根据character_set_client与character_set_connection的值选择是否进行转换，因为例子中两个变量值不同，因此'á'的编码转为使用latin1字符集，成了0xE1
• 经过解析语句，发现查询的列使用ucs2字符集，所以'á'的编码再一次发生变化，转为使用ucs2字符集，成了0x00E1
• 真正执行完语句会得到结果，结果中也包含了'á'这个字符串，使用的是ucs2字符集
• 在发送结果给客户端之前，会将结果中不是用character_set_results编码的部分进行转换，本例中就将结果中的字符串'á'转换为了utf8mb4字符集，成了0xC3A1
• 客户端接收到结果后，按照自身的utf8mb4字符集进行解码，再进行后续的处理

下面是复现该例的步骤：

变量具体含义

经过刚才例子相信你已经对几个变量的大致用途有了概念，这几个变量的具体含义如下：

• character_set_connection和collation_connection：该组变量被用于字符串字面量，当字符串字面量本身没有指定Charset和Collation的时候，就用character_set_connection和collation_connection作为该字面量的Charset和Collation。同时该组变量也被用于数字转字符串的时候，目标字符串的Charset和Collation也是该组变量。
• character_set_client：来自客户端的语句所使用的Charset，这个变量的Session值是客户端在连接到服务器时设置的(许多客户端支持一个--default-character-set选项来显式指定这个字符集)；当客户端请求的值未知或不可用(ucs2、utf16、utf16le、utf32)，或者根本没有请求设置Charset，或者服务器配置--skip-character-set-client-handshake忽略客户端请求时，变量的Global值被用来设置Session值。
• character_set_results：将查询结果返回给客户端时使用的Charset，包括结果数据如列值、结果元数据如列名以及错误信息，这不会影响查询过程，只是最后发送查询结果给客户端时可能会进行一次Charset转换。

大多数情况下，乱码就是因为客户端没有正确设置好character_set_client和character_set_results导致的，因为服务端只能依靠客户端传来的信息决定这两个变量的值，当客户端没有传或错传的时候，就会导致“服务端认为的”与“客户端实际的”驴唇不对马嘴，经过一系列Charset转换后不可预期的乱码就诞生了，这也是本文开篇提到的例1发生乱码的原因。

快捷命令

可以看出跟一个连接相关的变量主要是character_set_client、character_set_results、character_set_connection和collation_connection，为了方便MySQL有SET NAMES和SET CHARACTER SET两个命令可以一次性设置4个变量。

SET NAMES

SET NAMES 'charset_name'这一条语句等价于如下三条语句：

其中设置character_set_connection时会隐式将collation_connection设置为该Charset的默认Collation，如果想更细致地设置Collation，可以使用SET NAMES 'charset_name' COLLATE 'collation_name'指定。

SET CHARACTER SET

SET CHARACTER SET 'charset_name'这一条语句等价于如下三条语句：

与SET NAMES的唯一不同在于最后一句设置的是collation_connection，变量@@collation_database代表当前所在数据库(use db_name切换库)的默认Collation，这里设置collation_connection也会隐式地将character_set_connection修改为对应Charset。

5.3 配置库、表、列

库表列创建、修改时设置Charset和Collation的语法如下：

在配置时遵循以下规则：

• 如果创建时没有显式指定Charset和Collation：

• 库：使用character_set_server和collation_server作为自己默认的Charset和Collation
• 表：使用所在库的默认Charset和Collation作为自己默认的Charset和Collation
• 列：使用所在表的默认Charset和Collation作为自己的Charset和Collation

• 如果显式指定了Charset和Collation，那么使用显式指定的Charset和Collation
• 如果显式指定了Charset没有显式指定Collation，那么使用显式指定的Charset和其默认Collation
• 如果显式指定了Collation没有显式指定Charset，那么使用显式指定的Collation和其所属的Charset
• CHAR、VARCHAR、TEXT、ENUM、SET列都支持指定Charset和Collation
• 修改列的Charset时，MySQL会尝试映射数据值，但如果修改前后Charset不兼容，可能会发生数据丢失

5.4 字符串字面量

所有字符串字面量都有自己的Charset和Collation，可以如下显式指定，指定时Charset前需要加一个下划线，同时该语法也可以和b、X前缀搭配使用：

下面是一些示例：

在配置时遵循以下规则：

• 如果没有显式指定Charset和Collation，那么将使用character_set_connection和collation_connection作为其Charset和Collation，对于b、X前缀的字符串Charset和Collation将设置为binary
• 如果显式指定了Charset和Collation，那么使用显式指定的Charset和Collation
• 如果显式指定了Charset没有显式指定Collation，那么使用显式指定的Charset和其默认Collation
• 如果显式指定了Collation没有显式指定Charset，那么显式指定的Collation需要是character_set_connection的某个Collation -_charset_name被称为Introducer，没有Introducer的情况下解析器会把字符串字面量的字符集都转为character_set_connection，Introducer的作用是告诉解析器，紧随其后的字符串使用charset_name作为Charset，但它不会像CONVERT()函数那样将字符串转换为目标Charset，也不会改变字符串的二进制编码，只会在一些情况下进行padding，这也可能导致乱码：

• 大部分情况下_binary'11110000'和b'11110000'和X'F0'是等价的，都被认为是binary字符串，也可以使用，但在使用位运算的时候会有一些不同，如下面例子所示，b、X前缀的二进制字符串的位运算结果是数字，所以没有前导0；而binary前缀的二进制字符串的位运算结果也同样是字符串，所以有前导0：

5.5 错误信息的Charset

MySQL构建错误消息的方式如下：

• 错误消息模板使用utf8mb3字符集
• 当模板中的参数被替换为一些值时：

• 表名或列名等元信息，在内部同样使用utf8mb3，因此它们按原样被复制
• 非二进制Charset的字符串值从其Charset转换为utf8mb3
• 二进制字符串值在0x20到0x7E范围内的字节按原样复制，范围外的字节使用\x十六进制编码。例如，如果在尝试向VARBINARY唯一列中插入0x41CF9F时发生重复键错误，生成的错误信息会变成“Duplicate entry 'A\xCF\x9F' for key 1”, 后两个字节0xCF9F被十六进制字符串形式输出，而第一个字节0x41则被转为字符A

一旦错误消息构建完成，可以由MySQL写入Error Log或发送给客户端：

• 将错误消息写入Error Log时，会以构建时的utf8mb3字符集写入，无需转换
• 将错误消息发送给客户端程序时，会将错误消息从utf8mb3转换为由character_set_results系统变量指定的Charset，如果character_set_results的值为NULL或binary，则不进行转换；如果变量值是utf8mb3或utf8mb4，也不会发生转换
• 如果有字符无法在character_set_results中表示，转换过程中可能会发生一些编码，编码使用Unicode码点：

• 基本多语言平面(BMP)范围内的字符(0x0000到0xFFFF)使用\nnnn表示法编写
• BMP范围之外的字符(0x10000到0x10FFFF)使用+nnnnnn表示法编写

6. 转换Charset

在MySQL中，有很多情况下会发生转换Charset的行为，例如：

• • 将一列数据赋值到另一个使用不同Charset的列：

• 使用字符串字面量INSERT或UPDATE一个使用不同Charset的列：

• 数据库侧发送结果给客户端：

• 内置函数CONVERT与CAST：

• ALTER TABLE修改列定义中的Charset，后续小节详细介绍

• 表达式中存在不同字符集的字符串，后续小节详细介绍

6.1 修改列的Charset

要将二进制或非二进制字符串列转换为使用特定Charset，需要使用ALTER TABLE，为了能够成功转换，必须满足以下条件之一：

• 如果列的数据类型是BINARY，VARBINARY，BLOB，它包含的所有值都必须使用单一Charset进行编码，需要和目标Charset匹配。如果二进制列存储了使用多个Charset编码后的信息，MySQL无法知道哪些值使用的哪种Charset，将无法正确转换数据。
• 如果列的数据类型是CHAR，VARCHAR，TEXT，其内容应该使用原本列定义的Charset进行编码，而不是其他Charset。需要注意的是，如果列数据中某个字符并不是被新、旧Charset共同包含，那么转换的过程中可能发生数据丢失。

使用ALTER TABLE修改列定义后，有两个需要额外注意的点：

• 如果原本列类型是BINARY(50)，可以将其转换为CHAR(50)，但转换后的值末尾会用0x00字节填充，这可能不符合期望，要删除这些字节，可以使用TRIM()函数：

• 如果在最初创建列时指定了Charset、Collation等属性，那么在使用ALTER TABLE修改表时也应该指定这些属性。例如，如果列定义中指定了NOT NULL和一个明确的DEFAULT值，那么在ALTER TABLE语句中也应该提供它们，否则最终的列定义将不包括这些属性。

6.2 表达式中的Charset转换

在上面的例子中如果x列、字面量、z列都使用相同的Charset和Collation，那么上面的语句将没有任何歧义，但如果它们的Charset或Collation不同，那么以谁的Charset和Collation为准呢？为此MySQL定义了一些规则来消除这些歧义，可以通过COERCIBILITY函数查看优先级，值越低优先级越高：

• 显式的COLLATE子句的coercibility为0

• 两个具有不同Collation的字符串连接后得到的字符串的coercibility为1
• 列、存储过程参数或局部变量的Collation的coercibility为2
• 系统常量(如USER()或VERSION()函数返回的字符串)的coercibility为3。

• 字面量的Collation的coercibility为4

• 数值或时间值的Collation的coercibility为5，例如在CONCAT(1, 'abc')时会将数字隐式转换为字符串

• NULL或源自NULL的表达式的coercibility为6

基于以上规则，就可以选出coercibility最小也就是优先级最高的字符串，它的Charset和Collation将被用于表达式及结果，但是如果出现了两个字符串的coercibility一样的情况呢？那就需要如下规则：

• 如果两个字符串都使用或都没使用Unicode Charset，那么返回错误
• 如果一方使用Unicode Charset，另一方使用非Unicode Charset，那么使用Unicode Charset的一方将占优势，非Unicode Charset的一方将自动转换为Unicode Charset。这源于MySQL认为任何Charset所包含字符都可以看作Unicode Charset所包含字符的子集 下面是一些具体的示例：

6.3 源码实现

入口函数是位于sql-common/sql_string.cc的String::copy函数，其内主要是做了三件事：

1. 1. 检查是否需要转换字符集，不需要的话就直接做字符串copy
2. 2. 检查原字符集是否是binary字符集，是的话就字节级别copy并对齐
3. 3. 其他情况意味着需要字符集转换，进行转换并copy

下面详细看看字符集转换具体怎么做的，copy_and_convert函数内透传调用my_convert函数，该函数逐字符检查，假如是ascii字符就直接复制，不是的话调用my_convert_internal进行后续处理，该函数就是字符集转换的关键，它对每个字符做了以下几件事：

1. 按照原字符集规则解析二进制编码，并根据解析结果获得对应的Unicode码点：

a. 解析成功并且获得了Unicode码点，就进入第2步

b. 解析失败就将对应字节当作'?'字符，使用其Unicode码点U+003F

c. 解析成功，但是Unicode不包含对应字符，无法获得Unicode码点，把该字符当作'?'字符，使用其Unicode码点U+003F，这里一般不会发生，因为Unicode字符通常是最全面的

2. 将上一步获得的Unicode码点转换成目标字符集编码，如果目标字符集不包含该字符，就将其转为'?'字符

从源码可以看出两点需要注意的地方：

• • 原字符集为binary字符集时，需要格外注意，会不加检查的逐字节复制，这有可能导致乱码，如下例所示：

• 原字符集如果与Unicode或新字符集不完全兼容，那么不兼容的字符会被转成问号'?'，如下例所示：

7. Unicode字符串排序算法

7.1 一个例子

在真正介绍Unicode字符串排序算法原理前，先通过MySQL中的一个例子对字符串排序有一个初步概念。首先创建一个只有一个VARCHAR列的表，该列使用的Charset是utf8mb4，向表中插入 “rôle”、 “Role”、 “role”、 “roles”、“rule”五个字符串，下面是分别使用utf8mb4_0900_as_cs和utf8mb4_bin两种Collation的排序结果：

可以看出Collation的不同影响了排序结果，先解释一下utf8mb4_bin的结果，其实该Collation排序规则很简单，就是将字符都转为原本的Unicode码点，然后根据码点数组由前到后进行比较，因此大写字母R排在了小写字母r前面。

utf8mb4_0900_as_cs既是音调敏感又是大小写敏感，该Collation基于UCA(Unicode Collation Algorithm) 9.0.0实现，本节将会以该版本UCA为例进行介绍，比较两个字符串绝大多数情况分为三个层次：原始字符、音调、大小写，三个层次的优先级逐级递减，只有前一级完全相等时才会比较下一级，因此：

• 第一层级比较时“role”=“Role”=“rôle”<“roles”<“rule”
• 第二层级比较时“role”=“Role”<“rôle”
• 第三层级比较时“role”<“Role”

至此，经过三个层级的比较，完全决定出五个字符串的大小关系，本例中使用的Collation是带_as、_cs后缀的，对于_ai、_ci后缀的Collation，会取消对应层级的比较，比如utf8mb4_0900_ai_ci会认为“role”=“Role”=“rôle”<“roles”<“rule”，utf8mb4_0900_as_ci会认为“role”=“Role”<“rôle”<“roles”<“rule”。

7.2 权重表

从上面例子的介绍可以看出在UCA 9.0.0进行字符串比较时，会获得每个字符在三个层级的键，显然字符的Unicode码点已经无法满足需求，那么就需要一个权重表去记录每个字符在三个层级上的权重，每个版本的UCA都会有一个对应的DUCET(Default Unicode Collation Element Table)^[27]记录这些信息，不同语言的定制化Collation其实就是基于DUCET加了本语言的一些特定权重配置。

下面给出几个表项的示例：

上面示例中的五个字符分别是a、A、𝐴、á、⒜，其中第一列是字符的Unicode码点，之后每一个方括号[]包裹的包含三个数字的数组称为一个Collation Element，一个字符可能对应一或多个Collation Element，#是注释符，其后是对该字符的介绍信息。

Collation Element内的三个16进制数字从前到后分别代表了原始字符、音调、大小写三个层级的权重，'*'开头的数字一般对应于标点符号，在有的比较方式中会忽略，但是MySQL内并没有对标点符号特殊处理，因此⒜字符前后的()标点符号也参与排序。

下面稍微解释下á、⒜对应多个Collation Element的由来，UCA定义了将一个复杂字符分解(decomposition)的规则^[28]，因此如下表所示，分解得到简单字符的Collation Element List再组织起来就成了上面例子中最终的Collation Element(并不一定完全相等，可能伴随一些音调、大小写权重的变化)：

7.3 算法流程

有了权重表之后，就剩下该如何使用权重表了，特别是有些字符不止对应一个Collation Element，UCA会计算出整个字符串用于排序的Sort Key，下面用字符串"aáA"为例进行介绍，三个字符及其Collation Element如下：

1. 获得字符串中所有字符的Collation Element，并组成一个Collation Element List，本例如下：

2. 将所有第一层级的权重(略过为0的项)从前向后取出拼接在一起，本例如下：

• SortKey1=1C471C471C47

3. 如果是音调敏感(_as)，将所有第二层级的权重(略过为0的项)从前向后取出拼接在一起，本例如下：

• SortKey2=0020002000240020

4. 如果是大小写敏感(_cs)，将所有第三层级的权重(略过为0的项)从前向后取出拼接在一起，本例如下：

• SortKey3=0002000200020008

5. 最后根据是否音调、大小写敏感选择是否将SortKey2、SortKey3拼接，假设都敏感的情况下，如下拼接出最终的SortKey，拼接符为0000，本例如下：

• SortKey=SortKey1+0000+SortKey2+0000+SortKey3
• SortKey=1C471C471C470000002000200024002000000002000200020008

之后对于字符串的比较就全部根据Sort Key，本质是一个16bit整型数组，数组中越靠前的值在比较时优先级就越高，至此就已经实现了三个层级的比较架构。在MySQL中可以使用WEIGHT_STRING函数获得字符串的Sort Key，本例如下：

7.4 没有在权重表中的字符

虽然UCA提供的权重表包含了很多字符到其Collation Element的映射，但是并不是所有字符都在表中拥有一个条目，对于这些字符，UCA会按照一定规则生成其Collation Element。

UCA 9.0.0

如果字符需要生成权重，那么其对应两个Collation Element，形如[.AAAA.0020.0002][.BBBB.0000.0000]，其中AAAA和BBBB是根据字符的Unicode码点计算出来的，UCA 9.0.0规则的MySQL实现如下：

中日韩字符的Collation Element基本就是这样生成的，如“张”的Unicode码点是0x5F20，对应上面代码base就是FB40，那么aaaa就可以计算得到是0xFB40，bbbb就可以计算得到是0xDF20，因此“张”这个字符的Collation Element就是[.FB40.0020.0002][.DF20.0000.0000]，作为字符串来看它的Sort Key如下，与我们的推算相符：

UCA 4.0.0

对于utf8mb4非0900、520的Collation来说，它们使用的UCA 4.0.0，在该版本的UCA中仅支持为BMP字符(U+0000到U+FFFF)生成权重，所有非BMP字符(U+10000到U+10FFFF)的权重统一为0xFFFD，因此在utf8mb4_unicode_ci看来所有非BMP字符都是相等的，如下：

对于未明确指定权重的BMP字符来说，生成权重的规则和UCA 9.0.0类似，只是没有对非BMP字符的处理，如下：

7.5 MySQL中的_general与_unicode

对于MySQL中的Unicode字符集，普遍有一个_general Collation和一个_unicode Collation，对于utf8mb4字符集而言，这两个Collation分别是utf8mb4_general_ci和utf8mb4_unicode_ci。这两个Collation都是基于UCA 4.0.0，但是utf8mb4_general_ci可以看作是utf8mb4_unicode_ci的简化，utf8mb4_general_ci不支持一个字符映射到多个Collation Element，因此有些在DUCET中规定的规则在utf8mb4_general_ci上并不生效，下面是一个例子。

在UCA 4.0.0的权重表中，每个Collation Element有四个数值，第四个是Unicode码点，前三个数值的含义、使用方法与9.0.0一致，下面看看utf8mb4_general_ci与utf8mb4_unicode_ci的不同：

在CPU还没有很强大的年代，utf8mb4_general_ci由于简化了一些内容(伴随着准确性的损失)，相比utf8mb4_unicode_ci能够有更好的性能，但是随着CPU的发展，这里的性能提升已经不明显了，所以目前并不建议继续使用utf8mb4_general_ci。至于utf8mb4_unicode_ci，其基于的UCA 4.0.0相比utf8mb4_0900_xx基于的UCA 9.0.0也落后了很多，因此更加建议使用utf8mb4_0900_xx Collation，至于后缀是使用_ai_ci、_as_ci、_as_cs中的哪一个，就需要根据自身业务进行考量。

7.6 LIKE的特殊性

在上面例子中，描述的都是=、<、>这种常规的比较，直接用算好的Sort Key即可，因此会有utf8mb4_unicode_ci上'ß'='ss'的情况，一个字符也可以和两个字符相等，但是LIKE运算符就不允许这种情况了，LIKE只允许一对一的字符匹配，所以有下例所示：

8. binary Charset与_bin Collation

本节将介绍最直观的二进制比较方式，包括binary Charset的binary Collation与非二进制Charset的_bin Collation两类，同时它们两者也有一些不同。BINARY、VARBINARY和BLOB数据类型使用binary Charset以及binary Collation，binary字符串是字节序列，这些字节的数值决定了排序顺序。

CHAR、VARCHAR和TEXT数据类型大多数情况下使用了非二进制的Charset，对于大多数非二进制Charset，其上支持一个_bin结尾的二进制比较Collation。例如，latin1和big5的二进制Collation分别命名为latin1_bin和big5_bin。utf8mb4是一个例外，它有两个二进制Collation，utf8mb4_bin和utf8mb4_0900_bin。

8.1 基本比较单位

binary Charset的基本比较单位是字节，非二进制Charset的基本比较单位是字符，字节和字符的差别在于字符有可能是多个字节组成的。当binary Charset的binary Collation进行比较时，会逐字节比较其数值；当非二进制Charset的_bin Collation进行比较时，会逐字符比较其数值，对于Unicode Charset而言有如下规律：除utf8mb4_0900_bin外的_bin Collation会将字符转为Unicode码点进行比较，可能会加上前导0

utf8mb4_0900_bin会使用字符原本的二进制进行比较，因为utf-8编码顺序和Unicode码点顺序上一致，所以可以获得相同结果，同时速度会更快 下面以binary、utf8mb4_bin、utf8mb4_0900_bin三种Collation举例演示：

可以发现：

• binary Charset没有字符的概念，在它看来所有字符串都是字节序列，它看到的字节序列就是binary Collation对该字符串比较、排序时使用的权重key
• utf8mb4 Charset就有字符的概念，但是utf8mb4_bin和utf8mb4_0900_bin两种Collation的表现有些不同

• utf8mb4_bin会将所有字符转回Unicode码点并且补齐到3 byte(因为Unicode字符可以完全使用3 byte表示)，例子中一字节字符d转为了0x000064，三字节字符က转为了0x001000，因此可以清晰看出_bin Collation是以字符为基本单位进行比较的
• utf8mb4_0900_bin则不能明显体现出以字符为基本单位，它的表现和binary Collation类似，直接使用utf8mb4原始编码进行比较，由于本身编码设计就已经满足多字节字符二进制首字节数值更大，所以这里不进行补齐byte直接比较也是可以达到相同目标的

8.2 大小写转换

二进制字符串没有字符大小写的概念，而非二进制字符串哪怕使用了_bin Collation也可以使用大小写转换函数，如下例所示：

8.3 CHAR、VARCHAR、BINARY、VARBINARY

这四种数据类型和Charset、Collation的相关性很高，因此这里介绍一些关键点：

• CHAR(N)、VARCHAR(N)的N代表的是字符数，CHAR(N)预留的是“N*字符集最长字符字节数”个字节；BINARY(N)、VARBINARY(N)的N代表的是字节数，BINARY(N)预留的是N个字节。举个例子说明，假设一个列类型为CHAR(5)，使用的utf8mb4字符集，由于最长字符的字节数为4，所以每个CHAR(5)都会预留5*4=20字节；但是BINARY(5)就只会预留5字节。
• 列定义时CHAR BINARY和VARCHAR BINARY并不意味着和BINARY类型有什么关系，而是意味着使用字符集的_bin Collation。例如当默认Charset是utf8mb4时，CHAR(5) BINARY等于是CHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin的缩写。
• CHAR和BINARY在插入数据时，如果数据不够长，会进行padding，CHAR类型padding的是0x20(空格符)，BINARY类型padding的时0x00。在读取CHAR类型列的时候，MySQL会自动将所有padding的0x20摘掉，因此在MySQL看来，向CHAR列插入"a"和"a "后读取出来都是"a"，丢失了原本的空格。在读取BINARY列的时候会将完整的N个字节返回(包括padding的0x00)，如下例所示：

9. 总结

MySQL支持了很多Charset与Collation，并且允许用户在连接、Server、库、表、列、字面量多个层次上进行精细化配置，这有时会让用户眼花缭乱。本文对相关概念、语法、系统变量、影响范围都进行了详细介绍，并且列举了有可能让字符串发生字符集转换的情况，以及来自不同字符集的字符串进行比较等操作时遵循的规则。

对于最常用的基于Unicode的字符集，本文介绍了Unicode标准与MySQL中各个字符集的关系，尤其详细介绍了当前版本(8.0.34)默认字符集utf8mb4。为了能够理解MySQL如何比较字符串，本文还介绍了Unicode标准定义的字符串多级排序算法，并举例说明了一些可能让中文用户感到费解的例子。

最后本文还介绍了特殊的binary字符集，厘清了其与其他非二进制字符集的_bin Collation的异同。经过本文的介绍，相信你已经对MySQL的Charset和Collation有了比较清晰的认识，下面将汇总一些关键点。

9.1 乱码

客户端实际使用的字符集与登记在服务端的character_set_client、character_set_results变量不一致，“客户端使用的”和“服务端以为客户端使用的”不统一，造成后续的错误处理，如开篇例子所示：

character_set_client哪怕配置正确，Introducer没有正确使用也可能跳过检查、转换插入乱码数据，如下例所示：

配置完全正确，但是用了binary字符串插入数据，binary字符串向非binary字符串转换时是逐字节拷贝，没有合法性检查，可以插入乱码数据，如下例所示：

9.2 升级版本需要注意的问题

• MySQL 5.7中utf8mb4字符集的默认Collation是utf8mb4_general_ci，MySQL 8.0中utf8mb4字符集的默认Collation是utf8mb4_0900_ai_ci，两者主要有以下区别，需要注意：
• utf8mb4_general_ci不支持组合(combine，将两个或多个字符当成一个字符)这种高级比较，只支持逐字符比较，如下例：

utf8mb4_general_ci具有“PAD SPACE”属性，比较时会忽略尾部空格；utf8mb4_0900_ai_ci具有“NO PAD”属性，不会忽略尾部空格，如下例：

utf8mb4_general_ci基于UCA 4.0.0，utf8mb4_0900_ai_ci基于UCA 9.0.0，UCA 9.0.0具有更多的字符权重定义，对于非BMP平面字符，utf8mb4_0900_ai_ci也会为它们计算生成不同的权重，utf8mb4_general_ci则会认为这些字符全部相等，如下例：

9.3 使用建议

• 在应用的测试阶段，就应该测试好客户端所使用的编码，以及与服务端建连后是否正确设置了character_set_client、character_set_results变量，否则很容易出现乱码的情况；
• 一般情况下character_set_client、character_set_results、character_set_connection可以统一，使用SET NAMES命令可以一次性设置完成；
• 如果字符串字面量前有Introducer，那么解析器不会将该字符串转为character_set_connection指定的字符集，而是认为字符串使用Introducer指定的字符集，但如果客户端字符集和Introducer字符集不一致，Introducer不会对原始字符串进行字符集转换，这时字符串的二进制编码与字符集不匹配，很可能产生乱码；
• MySQL中表名、列名等元数据使用的是utf8mb3字符集，仅支持Unicode BMP平面字符，不要在这些元数据信息中使用辅助平面字符；
• 字符集A转换为字符集B时，不兼容的字符会被MySQL转换成'?'，这有可能导致唯一键冲突；
• 在绝大多数情况下，utf8mb4字符集搭配其默认的utf8mb4_0900_ai_ci Collation就可以满足需求，但作为中文用户如果不希望看到英文字母和很多特殊字符相等的情况，可以追加使用utf8mb4_0900_as_ci或utf8mb4_0900_as_cs完成不同层次的过滤；
• 如果目标是字节序列完全相同，可以使用utf8mb4_0900_bin Collation，既能在比较时直接使用字节进行比较，具有更好的性能，同时又可以使用utf8mb4这种非binary字符集上的UPPER()、LOWER()等辅助函数；
• 当不确定某个Collation上两个字符串的排序顺序时，可以使用WEIGHT_STRING函数看看字符串的Sort Key，来确定该Collation的表现是不是符合自己的预期；
• 当多元操作符进行字符串比较时，如果输入字符串并不是来自一个Charset或使用不同的Collation，将会根据一些规则选择最终使用的Collation，并发生字符集转换，如果不熟悉这些规则，最好加上COLLATE子句明确自己想用的Collation；
• 二进制字符集的字符串可以不加变换逐字节转为非二进制字符集的字符串，这也可能导致乱码，因此使用INSERT语句的时候，谨慎使用b、X前缀将二进制字符串字面量插入到非二进制字符集的列中。

作者简介

张熙哲，AliSQL研发人员。目前主要从事AliSQL性能优化相关的研发工作和RDS运维工作。

引用链接

Charset: 参考一

UCA 9.0.0: 参考二

UCA 5.2.0: 参考三

UCA 4.0.0: 参考四

Unicode: 参考五

UTF-8: 参考六

Unicode联盟: 参考七

汉字的码点空间: 参考八

基本汉字: 参考九

基本汉字补充: 参考十

扩展A: 参考十一

扩展B: 参考十二

扩展C: 参考十三

扩展D: 参考十四

康熙部首: 参考十五

部首扩展: 参考十六

兼容汉字: 参考十七

兼容扩展: 参考十八

PUA(GBK)部件: 参考十九

部件扩展: 参考二十

PUA增补: 参考二十一

汉字笔画: 参考二十二

汉字结构: 参考二十三

汉语注音: 参考二十四

注音扩展: 参考二十五

〇: 参考二十六

DUCET(Default Unicode Collation Element Table): 参考二十七

复杂字符分解(decomposition)的规则: 参考二十八

来源  |  阿里云开发者公众号

作者  |  竹兴