MySQL 8.4 Release Notes
MySQL 实现了几种类型的排序规则：MySQL implements several types of collations:
8 位字符集的简单排序规则Simple collations for 8-bit character sets
这种排序规则是通过使用一个包含 256 个权重的数组来实现的，该数组定义了从字符代码到权重的一一映射。latin1_swedish_ci是一个示例。它是一个不区分大小写的排序规则，因此字符的大写和小写版本具有相同的权重，并且比较时被视为相等。This kind of collation is implemented using an array of 256
weights that defines a one-to-one mapping from character codes
to weights. latin1_swedish_ci is an example.
It is a case-insensitive collation, so the uppercase and
lowercase versions of a character have the same weights and they
compare as equal.
mysql>SET NAMES 'latin1' COLLATE 'latin1_swedish_ci';Query OK, 0 rows affected (0.01 sec) mysql>SELECT HEX(WEIGHT_STRING('a')), HEX(WEIGHT_STRING('A'));+-------------------------+-------------------------+ | HEX(WEIGHT_STRING('a')) | HEX(WEIGHT_STRING('A')) | +-------------------------+-------------------------+ | 41 | 41 | +-------------------------+-------------------------+ 1 row in set (0.01 sec) mysql>SELECT 'a' = 'A';+-----------+ | 'a' = 'A' | +-----------+ | 1 | +-----------+ 1 row in set (0.12 sec)
有关实现说明，请参见第 12.14.3 节，“将简单排序规则添加到 8 位字符集”。For implementation instructions, see Section 12.14.3, “Adding a Simple Collation to an 8-Bit Character Set”.
8 位字符集的复杂排序规则Complex collations for 8-bit character sets
这种排序是通过 C 源文件中的函数实现的，这些函数定义了字符的排序方式，如 第 12.13 节，“添加字符集” 中所述。This kind of collation is implemented using functions in a C source file that define how to order characters, as described in Section 12.13, “Adding a Character Set”.
非 Unicode 多字节字符集的排序Collations for non-Unicode multibyte character sets
对于这种类型的排序，8 位（单字节）和多字节字符的处理方式不同。对于 8 位字符，字符代码以不区分大小写的方式映射到权重。（例如，单字节字符 'a' 和 'A' 的权重都是 0x41。）对于多字节字符，字符代码与权重之间有两种类型的关系：For this type of collation, 8-bit (single-byte) and multibyte
characters are handled differently. For 8-bit characters,
character codes map to weights in case-insensitive fashion. (For
example, the single-byte characters 'a' and
'A' both have a weight of
0x41.) For multibyte characters, there are
two types of relationship between character codes and weights:
权重等于字符编码。
sjis_japanese_ci是这种排序规则的一个例子。多字节字符'ぢ'的字符编码是0x82C0，权重也为0x82C0。Weights equal character codes.sjis_japanese_ciis an example of this kind of collation. The multibyte character'ぢ'has a character code of0x82C0, and the weight is also0x82C0.mysql>
CREATE TABLE t1(c1 VARCHAR(2) CHARACTER SET sjis COLLATE sjis_japanese_ci);Query OK, 0 rows affected (0.01 sec) mysql>INSERT INTO t1 VALUES ('a'),('A'),(0x82C0);Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>SELECT c1, HEX(c1), HEX(WEIGHT_STRING(c1)) FROM t1;+------+---------+------------------------+ | c1 | HEX(c1) | HEX(WEIGHT_STRING(c1)) | +------+---------+------------------------+ | a | 61 | 41 | | A | 41 | 41 | | ぢ | 82C0 | 82C0 | +------+---------+------------------------+ 3 rows in set (0.00 sec)字符编码与权重是一一对应的，但编码不一定等于权重。
gbk_chinese_ci是这种排序规则的一个例子。多字节字符'膰'的字符编码是0x81B0，但权重为0xC286。Character codes map one-to-one to weights, but a code is not necessarily equal to the weight.gbk_chinese_ciis an example of this kind of collation. The multibyte character'膰'has a character code of0x81B0but a weight of0xC286.mysql>
CREATE TABLE t1(c1 VARCHAR(2) CHARACTER SET gbk COLLATE gbk_chinese_ci);Query OK, 0 rows affected (0.33 sec) mysql>INSERT INTO t1 VALUES ('a'),('A'),(0x81B0);Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>SELECT c1, HEX(c1), HEX(WEIGHT_STRING(c1)) FROM t1;+------+---------+------------------------+ | c1 | HEX(c1) | HEX(WEIGHT_STRING(c1)) | +------+---------+------------------------+ | a | 61 | 41 | | A | 41 | 41 | | 膰 | 81B0 | C286 | +------+---------+------------------------+ 3 rows in set (0.00 sec)
有关实现说明，请参阅 第 12.13 节，“添加字符集”。For implementation instructions, see Section 12.13, “Adding a Character Set”.
Unicode 多字节字符集的排序规则Collations for Unicode multibyte character sets
其中一些排序基于 Unicode 排序算法（UCA），其他的则不是。Some of these collations are based on the Unicode Collation Algorithm (UCA), others are not.
非 UCA 排序规则从字符编码到权重是一一对应的。在 MySQL 中，这类排序规则不区分大小写且不区分重音。utf8mb4_general_ci 是一个例子：'a'、'A'、'À' 和 'á' 各自有不同的字符编码，但都具有 0x0041 的权重，并比较为相等。Non-UCA collations have a one-to-one mapping from character code
to weight. In MySQL, such collations are case-insensitive and
accent-insensitive. utf8mb4_general_ci is an
example: 'a', 'A',
'À', and 'á' each have
different character codes but all have a weight of
0x0041 and compare as equal.
mysql>SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci';Query OK, 0 rows affected (0.00 sec) mysql>CREATE TABLE t1(c1 CHAR(1) CHARACTER SET UTF8MB4 COLLATE utf8mb4_general_ci);Query OK, 0 rows affected (0.01 sec) mysql>INSERT INTO t1 VALUES ('a'),('A'),('À'),('á');Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql>SELECT c1, HEX(c1), HEX(WEIGHT_STRING(c1)) FROM t1;+------+---------+------------------------+ | c1 | HEX(c1) | HEX(WEIGHT_STRING(c1)) | +------+---------+------------------------+ | a | 61 | 0041 | | A | 41 | 0041 | | À | C380 | 0041 | | á | C3A1 | 0041 | +------+---------+------------------------+ 4 rows in set (0.00 sec)
MySQL 中基于 UCA 的排序规则具有以下特性：UCA-based collations in MySQL have these properties:
如果一个字符有权重，每个权重使用 2 个字节（16 位）。If a character has weights, each weight uses 2 bytes (16 bits).
一个字符可能没有权重（或权重为空）。在这种情况下，该字符是可忽略的。例如：“U 0000 NULL” 没有权重，是可忽略的。A character may have zero weights (or an empty weight). In this case, the character is ignorable. Example: "U+0000 NULL" does not have a weight and is ignorable.
一个字符可能只有一个权重。例如：
'a'的权重是0x0E33。A character may have one weight. Example:'a'has a weight of0x0E33.mysql>
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';Query OK, 0 rows affected (0.05 sec) mysql>SELECT HEX('a'), HEX(WEIGHT_STRING('a'));+----------+-------------------------+ | HEX('a') | HEX(WEIGHT_STRING('a')) | +----------+-------------------------+ | 61 | 0E33 | +----------+-------------------------+ 1 row in set (0.02 sec)一个字符可能有多个权重。这是扩展。例如：德文字母
'ß'（SZ 连字，或称尖 S） 的权重是0x0FEA0FEA。A character may have many weights. This is an expansion. Example: The German letter'ß'(SZ ligature, or SHARP S) has a weight of0x0FEA0FEA.mysql>
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';Query OK, 0 rows affected (0.11 sec) mysql>SELECT HEX('ß'), HEX(WEIGHT_STRING('ß'));+-----------+--------------------------+ | HEX('ß') | HEX(WEIGHT_STRING('ß')) | +-----------+--------------------------+ | C39F | 0FEA0FEA | +-----------+--------------------------+ 1 row in set (0.00 sec)多个字符可能只有一个权重。这是收缩。例如：
'ch'在捷克语中是一个单独的字母，其权重是0x0EE2。Many characters may have one weight. This is a contraction. Example:'ch'is a single letter in Czech and has a weight of0x0EE2.mysql>
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_czech_ci';Query OK, 0 rows affected (0.09 sec) mysql>SELECT HEX('ch'), HEX(WEIGHT_STRING('ch'));+-----------+--------------------------+ | HEX('ch') | HEX(WEIGHT_STRING('ch')) | +-----------+--------------------------+ | 6368 | 0EE2 | +-----------+--------------------------+ 1 row in set (0.00 sec)
多字符到多权重的映射也是可能的（这是收缩与扩展），但 MySQL 不支持。A many-characters-to-many-weights mapping is also possible (this is contraction with expansion), but is not supported by MySQL.
有关非 UCA 排序规则的实现说明，请参见 第 12.13 节，“添加字符集”。有关 UCA 排序规则，请参见 第 12.14.4 节，“向 Unicode 字符集添加 UCA 排序规则”。For implementation instructions, for a non-UCA collation, see Section 12.13, “Adding a Character Set”. For a UCA collation, see Section 12.14.4, “Adding a UCA Collation to a Unicode Character Set”.
杂项排序规则Miscellaneous collations
还有一些排序规则不属于前面提到的任何类别。There are also a few collations that do not fall into any of the previous categories.