MySQL 排序规则

1.简介

字符集是一组符号和编码。排序规则是一组用于比较字符集中的字符的规则。

每个 MySQL 字符集可以支持一个或者多个排序规则，用于定义每个字符的比较规则，包括是否区分大小写，是否区分重音等。

2.支持的排序规则

MySQL 使用 SHOW COLLATION 语句查看各种字符集支持的排序规则：

SHOW COLLATION
    [LIKE 'pattern' | WHERE expr]

比如：

SHOW COLLATION;
+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
...

| gb18030_chinese_ci       | gb18030  | 248 | Yes     | Yes      |       2 |
| gb18030_bin              | gb18030  | 249 |         | Yes      |       1 |
| gb18030_unicode_520_ci   | gb18030  | 250 |         | Yes      |       8 |
+--------------------------+----------+-----+---------+----------+---------+
222 rows in set (0.06 sec)

• Collation：排序规则的名称。这是排序规则的唯一标识符，您可以在创建或更改表时使用它来指定表的排序规则。
• Charset：字符集的名称。排序规则是与特定字符集关联的，该列显示了该排序规则适用的字符集。
• Id：排序规则的内部编号。这是MySQL内部使用的标识符。
• Default：是否为默认排序规则。如果是默认排序规则，将显示“Yes”；否则，显示“”No”。
• Compiled：是否已编译排序规则。编译的排序规则可以更快地执行字符排序操作。如果已编译，则显示“Yes”；否则，显示“”No”。
• Sortlen：显示了排序规则的最大前缀长度。在某些情况下，只需比较字符串的前几个字符即可确定排序顺序，这可以提高性能。Sortlen 列显示了应用此规则时要比较的字符数。

字符集至少有一个排序规则，大多数有多个。每个字符集都有一个默认排序规则，例如 utf8mb4 和 latin1 的默认排序规则为 utf8mb4_0900_ai_ci 和 latin1_swedish_ci。

或者从 INFORMATION_SCHEMA CHARACTER_SETS 视图中查看所有字符集与之对应的默认排序规则。

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;
+--------------------+----------------------+---------------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION                     | MAXLEN |
+--------------------+----------------------+---------------------------------+--------+
| big5               | big5_chinese_ci      | Big5 Traditional Chinese        |      2 |
| dec8               | dec8_swedish_ci      | DEC West European               |      1 |
| cp850              | cp850_general_ci     | DOS West European               |      1 |
...
| cp932              | cp932_japanese_ci    | SJIS for Windows Japanese       |      2 |
| eucjpms            | eucjpms_japanese_ci  | UJIS for Windows Japanese       |      3 |
| gb18030            | gb18030_chinese_ci   | China National Standard GB18030 |      4 |
+--------------------+----------------------+---------------------------------+--------+
41 rows in set (0.05 sec)

MySQL 8.0 默认使用 utf8mb4 字符集，默认的排序规则为 utf8mb4_0900_ai_ci，表示不区分重音和大小写。例如：

SELECT 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |
+-----------+
1 row in set (0.05 sec)

如果换成 utf8mb4_zh_0900_as_cs 排序规则，a 和 A 比较的结果如下：

SELECT 'a' COLLATE utf8mb4_zh_0900_as_cs = 'A';
+-----------------------------------------+
| 'a' COLLATE utf8mb4_zh_0900_as_cs = 'A' |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.05 sec)

3.设置排序规则

排序规则和字符集一样支持不同级别的设置。如果没有指定排序规则，MySQL 会基于字符集设置一个默认的排序规则。使用 SHOW 语句查看当前设置：

Variable_name                |Value             |
-----------------------------|------------------|
collation_connection         |utf8mb4_0900_ai_ci|
collation_database           |utf8mb4_0900_ai_ci|
collation_server             |utf8mb4_0900_ai_ci|
default_collation_for_utf8mb4|utf8mb4_0900_ai_ci|

其中，collation_server 与 character_set_server 对应，表示 MySQL 服务器的全局默认排序规则，可以在服务器配置文件中 [mysqld] 部分的 collation-server 选项或者启动服务时通过命令行参数 --collation-server 进行设置，也可以在运行时动态修改。

collation_database 和 character_set_database 对应，表示当前默认数据库的排序规则。当我们使用 CREATE DATABASE 或者 ALTER DATABASE 时，可以指定一个排序规则。

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

同样，对于 CREATE TABLE 和 ALTER TABLE 语句，也可以为表或者字符类型的字段指定一个排序规则。

CREATE TABLE table_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]

ALTER TABLE table_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

collation_connection 和 character_set_connection 对应，表示客户端连接使用的排序规则。

排序规则由字符集的名称、可选的本地语言代码和 Unicode 版本以及其他属性组成，例如 utf8mb4_zh_0900_as_cs 表示 9.0.0 版本 utf8mb4 字符集的中文排序规则，区分重音（accent sensitive）和大小写（case sensitive）。

4.查看排序规则

• 查看数据库的排序规则

您可以查询 information_schema 数据库的 SCHEMATA 视图来查看数据库的排序规则。

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'your_database_name';

• 查看数据表的排序规则

要查看特定数据表的排序规则，使用 SHOW TABLE STATUS 语句。

SHOW TABLE STATUS LIKE 'tbl_name';

也可以查询 information_schema 数据库的 TABLES 表，以获取有关数据表的信息。

SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

5.中文排序规则

对于中文而言，排序方式与英文有所不同。英文通常按照字母排序，而中文通常按照拼音、偏旁部首或者笔画进行排序。

MySQL 8.0 默认使用的排序规则 utf8mb4_0900_ai_ci 对于中文按照偏旁部首进行排序。以下语句按照员工的姓名进行排序：

SELECT name
FROM employee
ORDER BY name;
name     |
---------|
关兴      |
关平      |
关羽      |
刘备      |
周仓      |
孙丫鬟    |
孙乾      |
孙尚香    |
庞统      |
廖化      |
...

对于 utf8mb4 字符集，utf8mb4_zh_0900_as_cs 排序规则按照中文拼音进行排序。例如：

SELECT name
FROM employee
ORDER BY name collate 'utf8mb4_zh_0900_as_cs';
name      |
----------|
邓芝      |
法正      |
关平      |
关兴      |
关羽      |
黄权      |
黄忠      |
简雍      |
蒋琬      |
廖化      |
...

也可以将数据转换为其他支持特定排序规则的字符集，例如 gbk 字符集默认的 gbk_chinese_ci 排序规则就是按照拼音进行排序：

SELECT emp_name
FROM employee
ORDER BY convert(emp_name using gbk);

该语句和上一个示例返回的结果相同。

---

参考文献

Chapter 10 Character Sets, Collations, Unicode
13.7.7.38 SHOW TABLE STATUS Statement