MySQL中的字符集与排序规则

在MySQL中，当使用字符串类型时，有两方面的知识我们需要特别关注一下：字符集和排序规则。如果使用不当，则可能会导致性能问题或在插入数据时出现一些异常情况。

字符集定义了对应列允许使用的字符，而排序规则是用于比较这些字符的基础规则。通常，每个类型的字符集都会有多种排序规则，但一个排序规则只能属于一个字符集。

这篇文章，我们就围绕MySQL中字符集以及排序规则展开，详细聊聊相关的技术点。

MySQL中的字符集

MySQL支持广泛的字符集，包括GB2312、GBK、BIG5等本地字符集，以及多种Unicode字符集，如utf8mb3 (MySQL中旧版的utf8)、utf8mb4、ucs2、utf16和utf32。utf8mb4是推荐使用的字符集，因为它能完整支持所有Unicode字符，包括表情符号和不常用的汉字。

可以使用下面的命令在information_schema数据库中查看所有字符集：

1mysql> SELECT * FROM information_schema.character_sets ORDER BY character_set_name;
2+--------------------+----------------------+---------------------------------+--------+
3| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION                     | MAXLEN |
4+--------------------+----------------------+---------------------------------+--------+
5| armscii8           | armscii8_general_ci  | ARMSCII-8 Armenian              |      1 |
6| ascii              | ascii_general_ci     | US ASCII                        |      1 |
7| big5               | big5_chinese_ci      | Big5 Traditional Chinese        |      2 |
8| binary             | binary               | Binary pseudo charset           |      1 |
9……
10……
11……
12| utf16              | utf16_general_ci     | UTF-16 Unicode                  |      4 |
13| utf16le            | utf16le_general_ci   | UTF-16LE Unicode                |      4 |
14| utf32              | utf32_general_ci     | UTF-32 Unicode                  |      4 |
15| utf8               | utf8_general_ci      | UTF-8 Unicode                   |      3 |
16| utf8mb4            | utf8mb4_0900_ai_ci   | UTF-8 Unicode                   |      4 |
17+--------------------+----------------------+---------------------------------+--------+
18

通过上述命令可以列出当前MySQL支持的所有字符集，以及它们的默认排序规则。每种字符集都有一个默认排序规则。

在上述命令展示的列表底部，有两个字符集被描述为UTF-8 Unicode。utf8字符集的MAXLEN为3，而utf8mb4的MAXLEN为4。这里指的是每个字符允许的最大字节长度。

特别需要留意的是，根据UTF-8标准，每个字符允许使用最多4个字节，这意味着MySQL的utf8字符集实际上并不是真正的UTF-8，因为它只支持每字符最多3个字节。从MySQL 8开始，utf8mb4成为默认字符集，也是最常使用的字符集。而utf8被保留用于向下兼容，应该不再使用。

如何定义字符集

定义列的字符集有几种方式。如果你没有在表或列级别指定字符集，服务器默认的utf8mb4字符集将被应用（除非你明确声明了一个不同的服务器或数据库默认设置）。

我们可以通过创建一个没有字符集信息的表并读取其定义来验证这一点：

1CREATE TABLE no_charset (
2  my_column VARCHAR(255)
3);
4
5SHOW CREATE TABLE no_charset;
6

生成的CREATE TABLE语句显示了默认的字符集和排序规则已经被应用：

1CREATE TABLE `no_charset` (
2  `my_column` varchar(255) DEFAULT NULL
3) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
4

在表级别定义

你可以显式地在表级别设置字符集，通过使用CHARSET=[字符集]语法。例如，这里我们创建一个所有字符列都使用latin1字符集的表：

1CREATE TABLE no_charset (
2  `my_column` VARCHAR(255) DEFAULT NULL
3) ENGINE=InnoDB DEFAULT CHARSET=latin1;
4

在列级别定义

你也可以在列级别设置字符集。这是最具体的设置，会覆盖任何表级别的设置：

1CREATE TABLE `mixed_collations` (
2  `explicitly_set` VARCHAR(255) CHARACTER SET latin1,
3  `implicitly_set` VARCHAR(255)
4);
5

通过运行SHOW CREATE TABLE mixed_collations可以看到表的默认字符集是utf8mb4，但是显式设置的列使用了latin1字符集：

1CREATE TABLE `mixed_collations` (
2  `explicitly_set` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
3  `implicitly_set` VARCHAR(255) DEFAULT NULL
4) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
5

列级别的声明将覆盖表级别的声明，表级别的声明将覆盖数据库默认，数据库默认的字符集覆盖服务器默认。

MySQL中的排序规则

字符集定义了可以存储在列中的合法字符，而排序规则则是确定如何进行字符串比较的规则。如果你在排序或比较字符串，MySQL就会使用排序规则来决定顺序以及判断字符串是否相同。

可以通过查询information_schema表来显示所有排序规则。下面的查询仅显示与utf8mb4字符集相关的排序规则：

1mysql> SELECT
2    ->   *
3    -> FROM
4    ->   information_schema.collations
5    -> WHERE
6    ->   character_set_name = 'utf8mb4'
7    -> ORDER BY
8    ->   collation_name;
9+----------------------------+--------------------+-----+------------+-------------+---------+---------------+
10| COLLATION_NAME             | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
11+----------------------------+--------------------+-----+------------+-------------+---------+---------------+
12| utf8mb4_0900_ai_ci         | utf8mb4            | 255 | Yes        | Yes         |       0 | NO PAD        |
13| utf8mb4_0900_as_ci         | utf8mb4            | 305 |            | Yes         |       0 | NO PAD        |
14| utf8mb4_0900_as_cs         | utf8mb4            | 278 |            | Yes         |       0 | NO PAD        |
15| utf8mb4_0900_bin           | utf8mb4            | 309 |            | Yes         |       1 | NO PAD        |
16| utf8mb4_bin                | utf8mb4            |  46 |            | Yes         |       1 | PAD SPACE     |
17……
18……
19……
20

该查询将显示所有排序规则、相关字符集名称、是否为默认值以及其他信息。例如，utf8mb4_0900_ai_ci是utf8mb4字符集的默认排序规则。

排序规则的命名规则

排序规则的命名通常前缀为字符集名称，后缀由排序规则的属性组合而成。

以下是一些主要后缀及其含义：

后缀	含义
_ai	不区分重音符
_as	区分重音符
_ci	不区分大小写
_cs	区分大小写
_ks	区分假名
_bin	二进制比较

例如，默认排序规则utf8mb4_0900_ai_ci可以拆解：

utf8mb4：属于utf8mb4字符集；
0900：使用UCA 9.0.0的权重键；
_ai：不区分重音符；
_ci：不区分大小写。

那么，字符串比较是否区分大小写？答案是：“视具体排序规则而定！”

验证排序规则

我们可以使用COLLATE关键字显式设置字符串的排序规则：

1mysql> SELECT "MySQL" COLLATE utf8mb4_0900_ai_ci = "mysql" COLLATE utf8mb4_0900_ai_ci;
2+-------------------------------------------------------------------------+
3| "MySQL" COLLATE utf8mb4_0900_ai_ci = "mysql" COLLATE utf8mb4_0900_ai_ci |
4+-------------------------------------------------------------------------+
5|                                                                       1 |
6+-------------------------------------------------------------------------+
7

此查询返回结果值为1，表示MySQL认为这两个字符串相等。如果改用区分大小写的排序规则，我们会得到不同的结果：

1mysql> SELECT "MySQL" COLLATE utf8mb4_0900_as_cs = "mysql" COLLATE utf8mb4_0900_as_cs;
2+-------------------------------------------------------------------------+
3| "MySQL" COLLATE utf8mb4_0900_as_cs = "mysql" COLLATE utf8mb4_0900_as_cs |
4+-------------------------------------------------------------------------+
5|                                                                       0 |
6+-------------------------------------------------------------------------+
7

此查询返回结果值为0，表明MySQL认为这两个字符串不同，因为它们大小写不同。

类似逻辑也适用于重音符。例如，在使用不区分重音符的排序规则时，resume与résumé会被认为是相同的。

如何定义排序规则

与字符集类似，排序规则可以在表级别或列级别设置。如果未显式定义排序规则，MySQL会使用字符集的默认排序规则。

表级别定义

可以在CREATE TABLE语句中使用COLLATE子句定义表的排序规则。例如，以下创建了一个所有字符列都使用utf8mb4_bin排序规则的表：

1CREATE TABLE table_with_collation (
2  my_column VARCHAR(255)
3) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
4

列级别定义

也可以在列定义中设置排序规则。例如：

1CREATE TABLE table_with_collation (
2  `explicitly_set` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
3  `implicitly_set` varchar(255)
4) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
5

此外，也可以通过ALTER TABLE语句修改现有表列的排序规则：

1ALTER TABLE table_with_collation
2    CHANGE `explicitly_set` `explicitly_set` varchar(255)
3        CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
4

小结

理解字符集和排序规则是处理MySQL字符串数据的基础。字符集定义了列中存储的合法字符，排序规则决定了字符串比较的方式。

字符集可以在列级别、表级别定义，或者继承自数据库或服务器默认值。最具体的层级（列 > 表 > 数据库 > 服务器）将会被采用。
排序规则可以在列级别、表级别定义，或者继承自字符集默认。仍然是最具体的层级优先。

列的字符集和排序规则会影响数据存储方式以及数据的比较和排序行为。在设计数据库时需注意这些设置，以确保行为正确并优化性能。

如果不确定应使用哪个字符集或排序规则，MySQL默认的utf8mb4字符集及其默认排序规则utf8mb4_0900_ai_ci通常是一个不错的选择。它们支持所有Unicode字符并提供大小写不敏感和重音符不敏感的比较功能。

《MySQL中的字符集与排序规则》是转载文章，点击查看原文。