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