如何避免数据库乱码？一文剖析MySQL字符集与Collation（下）

引言

在你使用MySQL的时候，有没有遇到过字符串出现乱码的情况？

想查一个英文字符串，结果查出来了特殊字符？

字符串排序有时小写在前，有时大写在前？

如果在使用MySQL时不在意字符集、Collation等相关配置，那么就可能会碰到这些问题，本文将以几个例子作为开篇。

---

由于篇幅较长，本文分为上、下两篇介绍，本篇为下篇，重点介绍字符集转换与Unicode、Binary字符集的排序算法。

第一部分 （👉点击查看上篇：概念介绍与MySQL的配置项）

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

• 配置Charset和Collation

第二部分

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

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

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

-- 从latin1转换为utf8mb4
UPDATE t1 SET utf8mb4_bin_column=latin1_column;
-- 从utf8mb4转换为latin1
INSERT INTO t1 (latin1_column) SELECT utf8mb4_bin_column FROM t2;

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

SET NAMES latin1; -- 字面量使用latin1
-- 从latin1转换为utf8mb4
INSERT INTO t1 (utf8mb4_bin_column) VALUES ('string-in-latin1');

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

SET NAMES latin1; -- 客户端使用latin1
-- 结果字符串使用utf8mb4，需要转换为latin1再发送客户端
SELECT utf8mb4_bin_column FROM t2;

• • 内置函数CONVERT与CAST：

SELECT CONVERT(_latin1 'Müller' USING utf8mb4);
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4);

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

-- 从utf8mb4转换为latin1
ALTER TABLE t1 MODIFY utf8mb4_bin_column VARCHAR(10) CHARACTER SET latin1;
-- 将表内所有列的Charset转换为latin1
ALTER TABLE t1 CONVERT TO CHARACTER SET latin1;

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

-- 需要判断优先级，该例中latin1列数据从latin1转换为utf8mb4
SELECT CONCAT(utf8mb4_column, latin1_column) FROM t1;

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

• 如果列的数据类型是BINARY，VARBINARY，BLOB，它包含的所有值都必须使用单一Charset进行编码，需要和目标Charset匹配。如果二进制列存储了使用多个Charset编码后的信息，MySQL无法知道哪些值使用的哪种Charset，将无法正确转换数据。

• 如果列的数据类型是CHAR，VARCHAR，TEXT，其内容应该使用原本列定义的Charset进行编码，而不是其他Charset。需要注意的是，如果列数据中某个字符并不是被新、旧Charset共同包含，那么转换的过程中可能发生数据丢失。

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

UPDATE t SET col1 = TRIM(TRAILING 0x00 FROM col1);

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

SELECT x FROM T WHERE x = 'Y';
SELECT concat(x, 'Y', z) FROM T;

SELECT COERCIBILITY(_utf8mb4'A' COLLATE utf8mb4_bin); 
-- 返回0

• 列、存储过程参数或局部变量的Collation的coercibility为2；

• 系统常量(如USER()或VERSION()函数返回的字符串)的coercibility为3。

SELECT COERCIBILITY(VERSION()); -- 返回3

• 字面量的Collation的coercibility为4。

SELECT COERCIBILITY('A'); -- 返回4

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

SELECT COERCIBILITY(1000); -- 返回5

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

SELECT COERCIBILITY(NULL); -- 返回6

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

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

3. 其他情况意味着需要字符集转换，进行转换并copy。

bool String::copy(const char *str, size_t arg_length,
                  const CHARSET_INFO *from_cs, const CHARSET_INFO *to_cs,
                  uint *errors) {
  ...
  /* 1. 不需要转换就直接copy */
  if (!needs_conversion(arg_length, from_cs, to_cs, &offset)) {
    *errors = 0;
    return copy(str, arg_length, to_cs);
  }
  /* 2. 原字符集是binary字符集，字节级copy并对齐 */
  if ((from_cs == &my_charset_bin) && offset) {
    *errors = 0;
    return copy_aligned(str, arg_length, offset, to_cs);
  }
  size_t new_length = to_cs->mbmaxlen * arg_length;
  if (alloc(new_length)) return true;
  /* 3. 进行字符集转换并copy */
  m_length = copy_and_convert(m_ptr, new_length, to_cs, str, arg_length,
                              from_cs, errors);
  m_charset = to_cs;
  return false;
}

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

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

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

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

static size_t my_convert_internal(char *to, size_t to_length,
                                  const CHARSET_INFO *to_cs, const char *from,
                                  size_t from_length,
                                  const CHARSET_INFO *from_cs, uint *errors) {
  ...
  /* 每个字符集会定义自己的字符编码到Unicode码点、Unicode码点到字符编码的映射函数，
     这里需要原字符集的第一个函数，新字符集的第二个函数 */
  my_charset_conv_mb_wc mb_wc = from_cs->cset->mb_wc;
  my_charset_conv_wc_mb wc_mb = to_cs->cset->wc_mb;
  ...

  while (true) { /* 循环处理每个字符 */
    /* 1. 解析二进制编码到Unicode码点 */
    if ((cnvres = (*mb_wc)(from_cs, &wc, pointer_cast<const uchar *>(from),
                           from_end)) > 0) /* a. 成功*/
      from += cnvres;
    else if (cnvres == MY_CS_ILSEQ) { /* b. 解析失败 */
      error_count++;
      from++;
      wc = '?';
    } else if (cnvres > MY_CS_TOOSMALL) { /* c. 解析成功但是获取Unicode码点失败 */
      error_count++;
      from += (-cnvres);
      wc = '?';
    } else
      break;

  outp:
    /* 2. 将上一步获得的Unicode码点转换成目标字符集编码 */
    if ((cnvres = (*wc_mb)(to_cs, wc, (uchar *)to, to_end)) > 0) /* 成功 */
      to += cnvres;
    else if (cnvres == MY_CS_ILUNI && wc != '?') { /* 失败，将wc赋值为'?'(0x3F)，重试转换 */
      error_count++;
      wc = '?';
      goto outp;
    } else
      break;
  }
  ...
}

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

-- 1. 创建表t1，有一个使用latin1字符集的列
mysql> create table t1 (a varchar(5) character set latin1);

-- 2. 插入'张'，由于latin1字符集没有字符'张'，因此直接报错，无法插入成功
mysql> insert into t1 values('张');
ERROR 1366 (HY000): Incorrect string value: '\xE5\xBC\xA0' for column 'a' at row 1

-- 3. 使用二进制字面量字符串插入，成功了，哪怕latin1字符集根本没有字符编码是0xE5BCA0
mysql> insert into t1 values(X'E5BCA0'); -- 0xE5BCA0是'张'在utf8mb4字符集的编码
Query OK, 1 row affected (0.00 sec)

-- 4. 查询看下结果，返回乱码，存的内容就是传啥存啥
mysql> select a, hex(a) from t1;
+--------+--------+
| a      | hex(a) |
+--------+--------+
| å¼     | E5BCA0 |
+--------+--------+

-- latin1字符集没有字符'张'，转成了'?'，16进制为0x3F
mysql> select hex(cast(_utf8mb4'张' as char character set latin1));
+-------------------------------------------------------+
| hex(cast(_utf8mb4'张' as char character set latin1))  |
+-------------------------------------------------------+
| 3F                                                    |
+-------------------------------------------------------+

mysql> select * from role_table 
       order by str collate utf8mb4_0900_as_cs;
+-------+
| str   |
+-------+
| role  |
| Role  |
| rôle  |
| roles |
| rule  |
+-------+

mysql> select * from role_table 
       order by str collate utf8mb4_bin;
+-------+
| str   |
+-------+
| Role  |
| role  |
| roles |
| rule  |
| rôle  |
+-------+

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

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

至此，经过三个层级的比较，完全决定出五个字符串的大小关系，本例中使用的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”。

0061  ; [.1C47.0020.0002] # LATIN SMALL LETTER A
0041  ; [.1C47.0020.0008] # LATIN CAPITAL LETTER A
1D434 ; [.1C47.0020.000B] # MATHEMATICAL ITALIC CAPITAL A
00E1  ; [.1C47.0020.0002][.0000.0024.0002] # LATIN SMALL LETTER A WITH ACUTE249C  ; [*0317.0020.0004][.1C47.0020.0004][*0318.0020.0004] # PARENTHESIZED LATIN SMALL LETTER A

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

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

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

• SortKey1=1C471C471C47

• SortKey2=0020002000240020

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

• SortKey3=0002000200020008

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

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

=1C471C471C470000002000200024002000000002000200020008

-- 音调、大小写敏感
mysql> select hex(weight_string(_utf8mb4'aáA' collate utf8mb4_0900_as_cs));
+---------------------------------------------------------------+
| hex(weight_string(_utf8mb4'aáA' collate utf8mb4_0900_as_cs))  |
+---------------------------------------------------------------+
| 1C471C471C470000002000200024002000000002000200020008          |
+---------------------------------------------------------------+

-- 音调敏感，大小写不敏感
mysql> select hex(weight_string(_utf8mb4'aáA' collate utf8mb4_0900_as_ci));
+---------------------------------------------------------------+
| hex(weight_string(_utf8mb4'aáA' collate utf8mb4_0900_as_ci))  |
+---------------------------------------------------------------+
| 1C471C471C4700000020002000240020                              |
+---------------------------------------------------------------+

-- 音调、大小写不敏感
mysql> select hex(weight_string(_utf8mb4'aáA' collate utf8mb4_0900_ai_ci));
+---------------------------------------------------------------+
| hex(weight_string(_utf8mb4'aáA' collate utf8mb4_0900_ai_ci))  |
+---------------------------------------------------------------+
| 1C471C471C47                                                  |
+---------------------------------------------------------------+

因此当我们设定Collation为音调、大小写不敏感时，会有很多非预期的情况出现，这时"aaaa"和"aáA𝐴"会被认为相等，如下：

mysql> select _utf8mb4'aáA𝐴' collate utf8mb4_0900_ai_ci = 'aaaa';
+-----------------------------------------------------+
| _utf8mb4'aáA?' collate utf8mb4_0900_ai_ci = 'aaaa'  |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+

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

▶︎ UCA 9.0.0

static void set_implicit_weights(MY_UCA_ITEM *item, int code) {
  int base, aaaa, bbbb;
  if (code >= 0x17000 && code <= 0x18AFF)  // Tangut character
  {
    aaaa = 0xFB00;
    bbbb = (code - 0x17000) | 0x8000;
  } else {
    /* non-Core Han Unified Ideographs */
    if ((code >= 0x3400 && code <= 0x4DB5) ||
        (code >= 0x20000 && code <= 0x2A6D6) ||
        (code >= 0x2A700 && code <= 0x2B734) ||
        (code >= 0x2B740 && code <= 0x2B81D) ||
        (code >= 0x2B820 && code <= 0x2CEA1))
      base = 0xFB80;
    /* Core Han Unified Ideographs */
    else if ((code >= 0x4E00 && code <= 0x9FD5) ||
             (code >= 0xFA0E && code <= 0xFA29))
      base = 0xFB40;
    /* All other characters whose weight is unassigned */
    else
      base = 0xFBC0;
    aaaa = base + (code >> 15);
    bbbb = (code & 0x7FFF) | 0x8000;
  }

  item->weight[0] = aaaa;
  item->weight[1] = 0x0020;
  item->weight[2] = 0x0002;
  item->weight[3] = bbbb;
  item->weight[4] = 0x0000;
  item->weight[5] = 0x0000;

  item->num_of_ce = 2;
}

mysql> select hex(weight_string(_utf8mb4'张' collate utf8mb4_0900_as_cs));
+--------------------------------------------------------------+
| hex(weight_string(_utf8mb4'张' collate utf8mb4_0900_as_cs))  |
+--------------------------------------------------------------+
| FB40DF200000002000000002                                     |
+--------------------------------------------------------------+

▶︎ UCA 4.0.0

-- '𠀫'的Unicode码点为U+2002B，其在utf8mb4_unicode_ci下的权重为0xFFFD
mysql> select hex(weight_string(_utf8mb4'𠀫' collate utf8mb4_unicode_ci));
+------------------------------------------------------------+
| hex(weight_string(_utf8mb4'?' collate utf8mb4_unicode_ci)) |
+------------------------------------------------------------+
| FFFD                                                       |
+------------------------------------------------------------+

-- 在基于UCA 4.0.0的utf8mb4_unicode_ci看来，'𠀫'和'𠀪'相等
mysql> select _utf8mb4'𠀫' collate utf8mb4_unicode_ci = '𠀪';
+----------------------------------------------+
| _utf8mb4'?' collate utf8mb4_unicode_ci = '?' |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+

-- 在基于UCA 9.0.0的utf8mb4_0900_ai_ci看来，'𠀫'和'𠀪'不等
mysql> select _utf8mb4'𠀫' collate utf8mb4_0900_ai_ci = '𠀪';
+----------------------------------------------+
| _utf8mb4'?' collate utf8mb4_0900_ai_ci = '?' |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+

if (code >= 0x3400 && code <= 0x4DB5)
  base= 0xFB80; /* CJK Ideograph Extension */
else if (code >= 0x4E00 && code <= 0x9FA5)
  base= 0xFB40; /* CJK Ideograph */
else
  base= 0xFBC0; /* All other characters */
aaaa= base +  (code >> 15);
bbbb= (code & 0x7FFF) | 0x8000;

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

-- 1. utf8mb4_general_ci的Sort Key是字符还原后的Unicode码点，'ß'被还原为's'
mysql> select hex(weight_string(_utf8mb4'ß' collate utf8mb4_general_ci));
+-------------------------------------------------------------+
| hex(weight_string(_utf8mb4'ß' collate utf8mb4_general_ci))  |
+-------------------------------------------------------------+
| 0053                                                        |
+-------------------------------------------------------------+

mysql> select hex(weight_string(_utf8mb4's' collate utf8mb4_general_ci));
+------------------------------------------------------------+
| hex(weight_string(_utf8mb4's' collate utf8mb4_general_ci)) |
+------------------------------------------------------------+
| 0053                                                       |
+------------------------------------------------------------+

-- 2. utf8mb4_general_ci看来，'ß'与's'相等
mysql> select _utf8mb4'ß' collate utf8mb4_general_ci = 's';
+-----------------------------------------------+
| _utf8mb4'ß' collate utf8mb4_general_ci = 's'  |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+

-- 3. utf8mb4_unicode_ci的Sort Key与UCA 9.0.0类似，只是映射表不同，
--    允许一个字符映射为多个Collation Element
mysql> select hex(weight_string(_utf8mb4'ß' collate utf8mb4_unicode_ci));
+-------------------------------------------------------------+
| hex(weight_string(_utf8mb4'ß' collate utf8mb4_unicode_ci))  |
+-------------------------------------------------------------+
| 0FEA0FEA                                                    |
+-------------------------------------------------------------+

mysql> select hex(weight_string(_utf8mb4's' collate utf8mb4_unicode_ci));
+------------------------------------------------------------+
| hex(weight_string(_utf8mb4's' collate utf8mb4_unicode_ci)) |
+------------------------------------------------------------+
| 0FEA                                                       |
+------------------------------------------------------------+

-- 4. utf8mb4_unicode_ci看来，'ß'与'ss'相等，而不是与's'相等
mysql> select _utf8mb4'ß' collate utf8mb4_unicode_ci = 'ss';
+------------------------------------------------+
| _utf8mb4'ß' collate utf8mb4_unicode_ci = 'ss'  |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+

在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中的哪一个，就需要根据自身业务进行考量。

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

-- utf8mb4_unicode_ci上，'ß'既不LIKE 'ss'，也不LIKE 's'
mysql> select _utf8mb4'ß' collate utf8mb4_unicode_ci like 'ss';
+---------------------------------------------------+
| _utf8mb4'ß' collate utf8mb4_unicode_ci like 'ss'  |
+---------------------------------------------------+
|                                                 0 |
+---------------------------------------------------+
mysql> select _utf8mb4'ß' collate utf8mb4_unicode_ci like 's';
+--------------------------------------------------+
| _utf8mb4'ß' collate utf8mb4_unicode_ci like 's'  |
+--------------------------------------------------+
|                                                0 |
+--------------------------------------------------+

-- utf8mb4_general_ci上，'ß' LIKE 's'如同=比较符一样，因为它们是字符级别一对一相等
mysql> select _utf8mb4'ß' collate utf8mb4_general_ci like 's';
+--------------------------------------------------+
| _utf8mb4'ß' collate utf8mb4_general_ci like 's'  |
+--------------------------------------------------+
|                                                1 |
+--------------------------------------------------+

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

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举例演示：

-- 使用以下两个字符为例
mysql> select _utf8mb4 X'64';
+----------------+
| _utf8mb4 X'64' |
+----------------+
| d              |
+----------------+
mysql> select _utf8mb4 X'e18080';
+--------------------+
| _utf8mb4 X'e18080' |
+--------------------+
| က                 |
+--------------------+

-- 1. binary
mysql> select WEIGHT_STRING(_binary X'64');
+------------------------------------------------------------+
| WEIGHT_STRING(_binary X'64')                               |
+------------------------------------------------------------+
| 0x64                                                       |
+------------------------------------------------------------+
mysql> select WEIGHT_STRING(_binary X'e18080');
+--------------------------------------------------------------------+
| WEIGHT_STRING(_binary X'e18080')                                   |
+--------------------------------------------------------------------+
| 0xE18080                                                           |
+--------------------------------------------------------------------+

-- 2. utf8mb4_bin
mysql> select WEIGHT_STRING(_utf8mb4 X'64' collate utf8mb4_bin);
+------------------------------------------------------------------------------------------------------+
| WEIGHT_STRING(_utf8mb4 X'64' collate utf8mb4_bin)                                                    |
+------------------------------------------------------------------------------------------------------+
| 0x000064                                                                                             |
+------------------------------------------------------------------------------------------------------+
mysql> select WEIGHT_STRING(_utf8mb4 X'e18080' collate utf8mb4_bin);
+--------------------------------------------------------------------------------------------------------------+
| WEIGHT_STRING(_utf8mb4 X'e18080' collate utf8mb4_bin)                                                        |
+--------------------------------------------------------------------------------------------------------------+
| 0x001000                                                                                                     |
+--------------------------------------------------------------------------------------------------------------+

-- 3. utf8mb4_0900_bin
mysql> select WEIGHT_STRING(_utf8mb4 X'64' collate utf8mb4_0900_bin);
+----------------------------------------------------------------------------------------------------------------+
| WEIGHT_STRING(_utf8mb4 X'64' collate utf8mb4_0900_bin)                                                         |
+----------------------------------------------------------------------------------------------------------------+
| 0x64                                                                                                           |
+----------------------------------------------------------------------------------------------------------------+
mysql> select WEIGHT_STRING(_utf8mb4 X'e18080' collate utf8mb4_0900_bin);
+------------------------------------------------------------------------------------------------------------------------+
| WEIGHT_STRING(_utf8mb4 X'e18080' collate utf8mb4_0900_bin)                                                             |
+------------------------------------------------------------------------------------------------------------------------+
| 0xE18080                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------+

可以发现：

• binary Charset没有字符的概念，在它看来所有字符串都是字节序列，它看到的字节序列就是binary Collation对该字符串比较、排序时使用的权重key；

• utf8mb4_bin会将所有字符转回Unicode码点并且补齐到3 byte(因为Unicode字符可以完全使用3 byte表示)，例子中一字节字符d转为了0x000064，三字节字符က转为了0x001000，因此可以清晰看出_bin Collation是以字符为基本单位进行比较的。

• utf8mb4_0900_bin则不能明显体现出以字符为基本单位，它的表现和binary Collation类似，直接使用utf8mb4原始编码进行比较，由于本身编码设计就已经满足多字节字符二进制首字节数值更大，所以这里不进行补齐byte直接比较也是可以达到相同目标的。

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

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
mysql> SELECT LOWER('aA'), UPPER('zZ');
+-------------+-------------+
| LOWER('aA') | UPPER('zZ') |
+-------------+-------------+
| aa          | ZZ          |
+-------------+-------------+

mysql> SET NAMES binary;
mysql> SELECT LOWER('aA'), LOWER(CONVERT('aA' USING utf8mb4));
+-------------+------------------------------------+
| LOWER('aA') | LOWER(CONVERT('aA' USING utf8mb4)) |
+-------------+------------------------------------+
| aA          | aa                                 |
+-------------+------------------------------------+

这四种数据类型和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)，如下例所示：

mysql> CREATE TABLE t (c BINARY(3));

mysql> INSERT INTO t SET c = 'a';

-- 字符串'a'后被补足了两个0x00，在判断时不认为该列数据等于'a'，而是等于'a\0\0'
mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t;
+--------+---------+-------------+
| HEX(c) | c = 'a' | c = 'a\0\0' |
+--------+---------+-------------+
| 610000 |       0 |           1 |
+--------+---------+-------------+

• VARCHAR和VARBINARY就没有上面所述padding的步骤，因此VARCHAR存入"a "后读取出来仍然是"a "，下面是一个例子：

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));

mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');

-- VARCHAR列数据的尾部空格符能够返回，CHAR列数据的尾部空格符无法返回
mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab  )              | (ab)                |
+---------------------+---------------------+

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

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

最后本文还介绍了特殊的binary字符集，厘清了其与其他非二进制字符集的_bin Collation的异同。

经过本文的介绍，相信你已经对MySQL的Charset和Collation有了比较清晰的认识，下面将汇总一些关键点。

-- 1.创建表t1，有两列，一列使用utf8mb4字符集，一列使用latin1字符集
mysql> create table t1 (a char(5) character set utf8mb4, b char(5) character set latin1);

-- 2.客户端实际使用utf8mb4字符集，却在服务端登记的是latin1字符集
mysql> set names latin1;
-- 2.a 列a使用utf8mb4字符集，MySQL看来character_set_client和
-- character_set_connection都是latin1，多了一次latin1字符集到utf8mb4
-- 字符集的转换，哪怕客户端原始传的就是utf8mb4格式编码，经过这一次转换后
-- 结果不可预期
-- 2.b 哪怕列b的latin1字符集没有字符'张'也能成功插入，这是因为MySQL看来
-- character_set_client、character_set_connection和列b字符集全都一致
-- 是latin1，无需做检查和转换，直接插入
mysql> insert into t1 values('张','张');

-- 3.正常客户端查询数据
mysql> set names utf8mb4;
-- a列(utf8mb4字符集)内容完全错乱，b列(latin1字符集)内容是'张'的utf8mb4编码
mysql> select a,hex(a),b,hex(b) from t1;
+--------+--------------+--------+--------+
| a      | hex(a)       | b      | hex(b) |
+--------+--------------+--------+--------+
| å¼     | C3A5C2BCC2A0 | å¼     | E5BCA0 |

+--------+--------------+--------+--------+

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

-- 1.创建表t1，有一列使用latin1字符集
mysql> create table t1(a char(5) character set latin1);

-- 2.直接插入'张'不成功是因为latin1没有该字符，但是加上_latin1前缀就插入成功了，
--   这是由于Introducer让MySQL解析器误以为这个utf8mb4字符串是latin1字符集的，
--   后续插入相应列的时候就不加检查、转换了
mysql> insert into t1(a) values('张');
ERROR 1366 (HY000): Incorrect string value: '\xE5\xBC\xA0' for column 'a' at row 1
mysql> insert into t1(a) values(_latin1'张');
Query OK, 1 row affected (0.00 sec)

-- 3.select出来存入的是utf8mb4上'张'的编码0xE5BCA0，产生乱码
mysql> select a, hex(a) from t1;
+--------+--------+
| a      | hex(a) |
+--------+--------+
| å¼     | E5BCA0 |
+--------+--------+

-- 1. 创建表t1，有一个使用latin1字符集的列
mysql> create table t1 (a varchar(5) character set latin1);

-- 2. 插入'张'，由于latin1字符集没有字符'张'，因此直接报错，无法插入成功
mysql> insert into t1 values('张');
ERROR 1366 (HY000): Incorrect string value: '\xE5\xBC\xA0' for column 'a' at row 1

-- 3. 使用二进制字面量字符串插入，成功了，哪怕latin1字符集根本没有字符编码是0xE5BCA0
mysql> insert into t1 values(X'E5BCA0'); -- 0xE5BCA0是'张'在utf8mb4字符集的编码
Query OK, 1 row affected (0.00 sec)

-- 4. 查询看下结果，返回乱码，存的内容就是传啥存啥
mysql> select a, hex(a) from t1;
+--------+--------+
| a      | hex(a) |
+--------+--------+
| å¼     | E5BCA0 |
+--------+--------+

• utf8mb4_general_ci不支持组合(combine，将两个或多个字符当成一个字符)这种高级比较，只支持逐字符比较，如下例：

-- 1. utf8mb4_general_ci看来，'ß'与's'相等
mysql> select _utf8mb4'ß' collate utf8mb4_general_ci = 's';
+-----------------------------------------------+
| _utf8mb4'ß' collate utf8mb4_general_ci = 's'  |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+

-- 2. utf8mb4_0900_ai_ci看来，'ß'与'ss'相等，而不是与's'相等，支持更加复杂的组合比较
mysql> select _utf8mb4'ß' collate utf8mb4_0900_ai_ci = 'ss';
+------------------------------------------------+
| _utf8mb4'ß' collate utf8mb4_0900_ai_ci = 'ss'  |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+

-- 1. 具有'PAD SPACE'属性的utf8mb4_general_ci认为'a '='a'
mysql> SELECT 'a ' = 'a' COLLATE utf8mb4_general_ci;
+---------------------------------------+
| 'a ' = 'a' COLLATE utf8mb4_general_ci |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
-- 2. 具有'NO PAD'属性的utf8mb4_0900_ai_ci认为'a '!='a'
mysql> SELECT 'a ' = 'a' COLLATE utf8mb4_0900_ai_ci;
+---------------------------------------+
| 'a ' = 'a' COLLATE utf8mb4_0900_ai_ci |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

• 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则会认为这些字符全部相等，如下例：

-- 在基于UCA 4.0.0的utf8mb4_general_ci看来，'𠀫'和'𠀪'相等
mysql> select _utf8mb4'𠀫' collate utf8mb4_general_ci = '𠀪';
+----------------------------------------------+
| _utf8mb4'?' collate utf8mb4_general_ci = '?' |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+

-- 在基于UCA 9.0.0的utf8mb4_0900_ai_ci看来，'𠀫'和'𠀪'不等
mysql> select _utf8mb4'𠀫' collate utf8mb4_0900_ai_ci = '𠀪';
+----------------------------------------------+
| _utf8mb4'?' collate utf8mb4_0900_ai_ci = '?' |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+

• 在应用的测试阶段，就应该测试好客户端所使用的编码，以及与服务端建连后是否正确设置了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前缀将二进制字符串字面量插入到非二进制字符集的列中。

引用链接

[1] DUCET(Default Unicode Collation Element Table): https://www.unicode.org/Public/UCA/9.0.0/allkeys.txt

点击了解 云数据库RDS

喜欢就请 点赞/分享/推荐 哦