MySQL 根据 http://www.unicode.org/reports/tr10/ 所述的 Unicode 排序算法（UCA）实现了 xxx_unicode_ci 排序规则。该排序规则使用版本 4.0.0 的 UCA 权重键：http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt。xxx_unicode_ci 排序规则仅部分支持 Unicode 排序算法。一些字符不被支持，组合符也未完全支持。这会影响如越南语、约鲁巴语和纳瓦荷语等语言。在字符串比较中，组合字符被认为不同于用单个 Unicode 字符书写的相同字符，并且这两个字符被认为长度不同（例如，通过 CHAR_LENGTH() 函数返回的长度或者在结果集元数据中）。MySQL implements the xxx_unicode_ci collations according to the Unicode Collation Algorithm (UCA) described at http://www.unicode.org/reports/tr10/. The collation uses the version-4.0.0 UCA weight keys: http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt. The xxx_unicode_ci collations have only partial support for the Unicode Collation Algorithm. Some characters are not supported, and combining marks are not fully supported. This affects languages such as Vietnamese, Yoruba, and Navajo. A combined character is considered different from the same character written with a single unicode character in string comparisons, and the two characters are considered to have a different length (for example, as returned by the CHAR_LENGTH() function or in result set metadata).

基于高于 4.0.0 版本的 UCA 的 Unicode 排序规则在排序名称中包含版本号。示例：Unicode collations based on UCA versions higher than 4.0.0 include the version in the collation name. Examples:

LOWER() 和 UPPER() 函数根据参数的排序规则执行大小写折叠。只有在高于 4.0.0 版本的 Unicode 中才存在的大写和小写版本的字符，只有当参数排序规则使用足够高版本的 UCA 时，这些函数才会转换该字符。The LOWER() and UPPER() functions perform case folding according to the collation of their argument. A character that has uppercase and lowercase versions only in a Unicode version higher than 4.0.0 is converted by these functions only if the argument collation uses a high enough UCA version.

基于 UCA 9.0.0 及更高版本的排序规则比基于 UCA 9.0.0 之前版本的排序规则更快。它们还有一个 NO PAD 的填充属性，而基于 UCA 9.0.0 之前版本的排序规则使用的是 PAD SPACE。对于非二进制字符串的比较，NO PAD 排序规则将字符串末尾的空格视为普通字符（参见 比较中的尾部空格处理）。Collations based on UCA 9.0.0 and higher are faster than collations based on UCA versions prior to 9.0.0. They also have a pad attribute of NO PAD, in contrast to PAD SPACE as used in collations based on UCA versions prior to 9.0.0. For comparison of nonbinary strings, NO PAD collations treat spaces at the end of strings like any other character (see Trailing Space Handling in Comparisons).

要确定排序规则的填充属性，请使用 INFORMATION_SCHEMA COLLATIONS 表，该表有一个 PAD_ATTRIBUTE 列。例如：To determine the pad attribute for a collation, use the INFORMATION_SCHEMA COLLATIONS table, which has a PAD_ATTRIBUTE column. For example:

mysql> SELECT COLLATION_NAME, PAD_ATTRIBUTE
       FROM INFORMATION_SCHEMA.COLLATIONS
       WHERE CHARACTER_SET_NAME = 'utf8mb4';
+----------------------------+---------------+
| COLLATION_NAME             | PAD_ATTRIBUTE |
+----------------------------+---------------+
| utf8mb4_general_ci         | PAD SPACE     |
| utf8mb4_bin                | PAD SPACE     |
| utf8mb4_unicode_ci         | PAD SPACE     |
| utf8mb4_icelandic_ci       | PAD SPACE     |
...
| utf8mb4_0900_ai_ci         | NO PAD        |
| utf8mb4_de_pb_0900_ai_ci   | NO PAD        |
| utf8mb4_is_0900_ai_ci      | NO PAD        |
...
| utf8mb4_ja_0900_as_cs      | NO PAD        |
| utf8mb4_ja_0900_as_cs_ks   | NO PAD        |
| utf8mb4_0900_as_ci         | NO PAD        |
| utf8mb4_ru_0900_ai_ci      | NO PAD        |
| utf8mb4_ru_0900_as_cs      | NO PAD        |
| utf8mb4_zh_0900_as_cs      | NO PAD        |
| utf8mb4_0900_bin           | NO PAD        |
+----------------------------+---------------+

非二进制字符串值（CHAR、VARCHAR 和 TEXT）在具有 NO PAD 校对规则时，与其他校对规则在尾随空格处理上有所不同。例如，'a' 和 'a ' 被比较时视为不同的字符串，而不是相同的字符串。这可以通过 utf8mb4 的二进制校对规则看到。utf8mb4_bin 的填充属性是 PAD SPACE，而 utf8mb4_0900_bin 的填充属性是 NO PAD。因此，涉及 utf8mb4_0900_bin 的操作不会添加尾随空格，并且涉及尾随空格的字符串比较在这两种校对规则下可能不同：Comparison of nonbinary string values (CHAR, VARCHAR, and TEXT) that have a NO PAD collation differ from other collations with respect to trailing spaces. For example, 'a' and 'a ' compare as different strings, not the same string. This can be seen using the binary collations for utf8mb4. The pad attribute for utf8mb4_bin is PAD SPACE, whereas for utf8mb4_0900_bin it is NO PAD. Consequently, operations involving utf8mb4_0900_bin do not add trailing spaces, and comparisons involving strings with trailing spaces may differ for the two collations:

mysql> CREATE TABLE t1 (c CHAR(10) COLLATE utf8mb4_bin);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES('a');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1 WHERE c = 'a ';
+------+
| c    |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 MODIFY c CHAR(10) COLLATE utf8mb4_0900_bin;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 WHERE c = 'a ';
Empty set (0.00 sec)

如果仅基于 Unicode 排序算法（UCA）的排序对某种语言效果不佳，MySQL 会实现特定语言的 Unicode 排序规则。特定语言排序是基于 UCA 的，并具有额外的语言定制规则。本节后面将出现此类规则的示例。关于特定语言排序的问题，http://unicode.org 提供了通用区域设置数据存储（CLDR）排序图表，链接为 http://www.unicode.org/cldr/charts/30/collation/index.html。MySQL implements language-specific Unicode collations if the ordering based only on the Unicode Collation Algorithm (UCA) does not work well for a language. Language-specific collations are UCA-based, with additional language tailoring rules. Examples of such rules appear later in this section. For questions about particular language orderings, http://unicode.org provides Common Locale Data Repository (CLDR) collation charts at http://www.unicode.org/cldr/charts/30/collation/index.html.

例如，非特定语言的 utf8mb4_0900_ai_ci 和特定语言的 utf8mb4_LOCALE_0900_ai_ci Unicode 排序规则都有以下特点：For example, the nonlanguage-specific utf8mb4_0900_ai_ci and language-specific utf8mb4_LOCALE_0900_ai_ci Unicode collations each have these characteristics:

包含下表中显示的区域代码或语言名称的排序规则名称是特定语言的排序规则。Unicode 字符集可能包含一个或多个这些语言的排序规则。A collation name that includes a locale code or language name shown in the following table is a language-specific collation. Unicode character sets may include collations for one or more of these languages.

MySQL 提供保加利亚语排序规则 utf8mb4_bg_0900_ai_ci 和 utf8mb4_bg_0900_as_cs。MySQL provides the Bulgarian collations utf8mb4_bg_0900_ai_ci and utf8mb4_bg_0900_as_cs.

克罗地亚语排序规则针对这些克罗地亚字母进行了优化：Č、Ć、Dž、Đ、Lj、Nj、Š、Ž。Croatian collations are tailored for these Croatian letters: Č, Ć, Dž, Đ, Lj, Nj, Š, Ž.

当使用拉丁字母书写时，MySQL 为塞尔维亚语提供 utf8mb4_sr_latn_0900_ai_ci 和 utf8mb4_sr_latn_0900_as_cs 排序规则，为波斯尼亚语提供 utf8mb4_bs_0900_ai_ci 和 utf8mb4_bs_0900_as_cs 排序规则。MySQL provides the utf8mb4_sr_latn_0900_ai_ci and utf8mb4_sr_latn_0900_as_cs collations for Serbian and the utf8mb4_bs_0900_ai_ci and utf8mb4_bs_0900_as_cs collations for Bosnian, when these languages are written with the Latin alphabet.

MySQL 为挪威语的两大主要变体提供了排序规则：对于博克 mål，你可以使用 utf8mb4_nb_0900_ai_ci 和 utf8mb4_nb_0900_as_cs；对于新挪威语，MySQL 现在提供 utf8mb4_nn_0900_ai_ci 和 utf8mb4_nn_0900_as_cs。MySQL provides collations for both major varieties of Norwegian: for Bokmål, you can use utf8mb4_nb_0900_ai_ci and utf8mb4_nb_0900_as_cs; for Nynorsk, MySQL now provides utf8mb4_nn_0900_ai_ci and utf8mb4_nn_0900_as_cs.

对于日语，utf8mb4 字符集包括 utf8mb4_ja_0900_as_cs 和 utf8mb4_ja_0900_as_cs_ks 排序规则。两种排序规则都区分重音和大小写。utf8mb4_ja_0900_as_cs_ks 同时区分假名，并区分片假名和平假名字符，而 utf8mb4_ja_0900_as_cs 在排序时将片假名和平假名字符视为相等。需要日语排序规则但不要求假名敏感的应用程序可以使用 utf8mb4_ja_0900_as_cs 来获得更好的排序性能。utf8mb4_ja_0900_as_cs 使用三级权重进行排序；utf8mb4_ja_0900_as_cs_ks 使用四级。For Japanese, the utf8mb4 character set includes utf8mb4_ja_0900_as_cs and utf8mb4_ja_0900_as_cs_ks collations. Both collations are accent-sensitive and case-sensitive. utf8mb4_ja_0900_as_cs_ks is also kana-sensitive and distinguishes Katakana characters from Hiragana characters, whereas utf8mb4_ja_0900_as_cs treats Katakana and Hiragana characters as equal for sorting. Applications that require a Japanese collation but not kana sensitivity may use utf8mb4_ja_0900_as_cs for better sort performance. utf8mb4_ja_0900_as_cs uses three weight levels for sorting; utf8mb4_ja_0900_as_cs_ks uses four.

对于不区分重音的古典拉丁语排序，I 和 J 被视为相等，U 和 V 也被视为相等。在基本字母层面上，I 与 J，以及 U 与 V 被视为相等。换句话说，J 被视为带重音的 I，U 被视为带重音的 V。For Classical Latin collations that are accent-insensitive, I and J compare as equal, and U and V compare as equal. I and J, and U and V compare as equal on the base letter level. In other words, J is regarded as an accented I, and U is regarded as an accented V.

MySQL 提供了用于使用西里尔字母书写的蒙古语的排序规则，utf8mb4_mn_cyrl_0900_ai_ci 和 utf8mb4_mn_cyrl_0900_as_cs。MySQL provides collations for the Mongolian language when written with Cyrillic characters, utf8mb4_mn_cyrl_0900_ai_ci and utf8mb4_mn_cyrl_0900_as_cs.

现代和传统西班牙语都提供西班牙语排序规则。对于两者，ñ（n 带波浪号）是介于n和o之间的独立字母。此外，对于传统西班牙语，ch是介于c和d之间的独立字母，ll是介于l和m之间的独立字母。Spanish collations are available for modern and traditional Spanish. For both, ñ (n-tilde) is a separate letter between n and o. In addition, for traditional Spanish, ch is a separate letter between c and d, and ll is a separate letter between l and m.

传统西班牙语排序规则也可用于阿斯图里亚语和加利西亚语。MySQL 还为加利西亚语提供utf8mb4_gl_0900_ai_ci和utf8mb4_gl_0900_as_cs排序规则。（它们分别与utf8mb4_es_0900_ai_ci和utf8mb4_es_0900_as_cs排序规则相同。）Traditional Spanish collations may also be used for Asturian and Galician. MySQL also provides utf8mb4_gl_0900_ai_ci and utf8mb4_gl_0900_as_cs collations for Galician. (These are the same collations as utf8mb4_es_0900_ai_ci and utf8mb4_es_0900_as_cs, respectively.)

瑞典语排序包括瑞典语规则。例如，在瑞典语中，以下关系成立，这并不是德国人或法国人所预期的：Swedish collations include Swedish rules. For example, in Swedish, the following relationship holds, which is not something expected by a German or French speaker:

Ü = Y < Ö

对于任何 Unicode 字符集，使用 xxx_general_ci 排序规则进行的操作比使用 xxx_unicode_ci 排序规则的操作更快。例如，utf8mb4_general_ci 排序规则的比较速度更快，但相比 utf8mb4_unicode_ci，准确性略低。原因是 utf8mb4_unicode_ci 支持扩展映射；也就是说，当一个字符可以与其他字符的组合进行等值比较时。例如，在德语和其他一些语言中，ß 等于 ss。utf8mb4_unicode_ci 还支持缩约和可忽略字符。utf8mb4_general_ci 是一种遗留排序规则，不支持扩展、缩约或可忽略字符。它只能进行字符的一对一比较。For any Unicode character set, operations performed using the xxx_general_ci collation are faster than those for the xxx_unicode_ci collation. For example, comparisons for the utf8mb4_general_ci collation are faster, but slightly less correct, than comparisons for utf8mb4_unicode_ci. The reason is that utf8mb4_unicode_ci supports mappings such as expansions; that is, when one character compares as equal to combinations of other characters. For example, ß is equal to ss in German and some other languages. utf8mb4_unicode_ci also supports contractions and ignorable characters. utf8mb4_general_ci is a legacy collation that does not support expansions, contractions, or ignorable characters. It can make only one-to-one comparisons between characters.

为进一步说明，以下等式在 utf8mb4_general_ci 和 utf8mb4_unicode_ci 中都成立（有关在比较或搜索中此效果的影响，请参见 第 12.8.6 节，“排序规则效果示例”）：To further illustrate, the following equalities hold in both utf8mb4_general_ci and utf8mb4_unicode_ci (for the effect of this in comparisons or searches, see Section 12.8.6, “Examples of the Effect of Collation”):

Ä = A
Ö = O
Ü = U

排序规则之间的一个区别是，对于 utf8mb4_general_ci，这是正确的：A difference between the collations is that this is true for utf8mb4_general_ci:

ß = s

而对于 utf8mb4_unicode_ci，这是正确的，它支持德国语 DIN-1 排序（也称为字典顺序）：Whereas this is true for utf8mb4_unicode_ci, which supports the German DIN-1 ordering (also known as dictionary order):

ß = ss

如果使用utf8mb4_unicode_ci进行排序对某种语言效果不佳，MySQL 会实现针对语言的 Unicode 排序规则。例如，utf8mb4_unicode_ci对德语字典序和法语排序都很好，因此无需创建特殊的utf8mb4排序规则。MySQL implements language-specific Unicode collations if the ordering with utf8mb4_unicode_ci does not work well for a language. For example, utf8mb4_unicode_ci works fine for German dictionary order and French, so there is no need to create special utf8mb4 collations.

utf8mb4_general_ci对德语和法语也都可以接受，只是ß等同于s，而不是ss。如果这对于您的应用是可以接受的，应使用utf8mb4_general_ci，因为它更快。如果不可接受（例如，如果您需要德语字典序），应使用utf8mb4_unicode_ci，因为它更准确。utf8mb4_general_ci also is satisfactory for both German and French, except that ß is equal to s, and not to ss. If this is acceptable for your application, you should use utf8mb4_general_ci because it is faster. If this is not acceptable (for example, if you require German dictionary order), use utf8mb4_unicode_ci because it is more accurate.

如果您需要德国 DIN-2（电话簿）排序，请使用 utf8mb4_german2_ci 校对规则，它将下列字符集视为相等：If you require German DIN-2 (phone book) ordering, use the utf8mb4_german2_ci collation, which compares the following sets of characters equal:

Ä = Æ = AE
Ö = Œ = OE
Ü = UE
ß = ss

utf8mb4_german2_ci 类似于 latin1_german2_ci，但后者不会将 Æ 与 AE 视为相等，也不会将 Œ 与 OE 视为相等。没有对应于 latin1_german_ci 的 utf8mb4_german_ci 用于德国字典顺序，因为 utf8mb4_general_ci 已足够。utf8mb4_german2_ci is similar to latin1_german2_ci, but the latter does not compare Æ equal to AE or Œ equal to OE. There is no utf8mb4_german_ci corresponding to latin1_german_ci for German dictionary order because utf8mb4_general_ci suffices.

一个字符的排序权重是按如下方式确定的：A character's collating weight is determined as follows:

可以使用 WEIGHT_STRING() 函数显示排序权重。（参见 第 14.8 节，“字符串函数和操作符”。）如果排序规则使用权重查找表，但某个字符不在表中（例如，因为它是一个 “新” 字符），则排序权重的确定会变得更复杂：Collating weights can be displayed using the WEIGHT_STRING() function. (See Section 14.8, “String Functions and Operators”.) If a collation uses a weight lookup table, but a character is not in the table (for example, because it is a “new” character), collating weight determination becomes more complex:

There is a difference between “ordering by the character's code value”以及“ordering by the character's binary representation,” a difference that appears only with utf16_bin, because of surrogates.There is a difference between “ordering by the character's code value” and “ordering by the character's binary representation,” a difference that appears only with utf16_bin, because of surrogates.

假设 utf16_bin（utf16 的二进制校对规则）是按二进制进行比较“逐字节””，而不是按“逐字符””进行比较。如果是这样，utf16_bin 中字符的顺序将与 utf8mb4_bin 中的顺序不同。例如，下表显示了两个罕见字符。第一个字符在 E000-FFFF 范围内，因此它大于代理项但小于补充字符。第二个字符是一个补充字符。Suppose that utf16_bin (the binary collation for utf16) was a binary comparison “byte by byte” rather than “character by character.” If that were so, the order of characters in utf16_bin would differ from the order in utf8mb4_bin. For example, the following chart shows two rare characters. The first character is in the range E000-FFFF, so it is greater than a surrogate but less than a supplementary. The second character is a supplementary.

Code point  Character                    utf8mb4      utf16
----------  ---------                    -------      -----
0FF9D       HALFWIDTH KATAKANA LETTER N  EF BE 9D     FF 9D
10384       UGARITIC LETTER DELTA        F0 90 8E 84  D8 00 DF 84

图表中的两个字符按代码点值排序，因为 0xff9d < 0x10384。它们按照 utf8mb4 值的顺序排列，因为 0xef < 0xf0。但是如果我们按字节逐个比较，它们并没有按 utf16 值的顺序排列，因为 0xff > 0xd8。The two characters in the chart are in order by code point value because 0xff9d < 0x10384. And they are in order by utf8mb4 value because 0xef < 0xf0. But they are not in order by utf16 value, if we use byte-by-byte comparison, because 0xff > 0xd8.

所以 MySQL 的 utf16_bin 排序规则并不是 “逐字节”。它是 “按代码点”。当 MySQL 在 utf16 中看到一个补充字符编码时，它会转换为该字符的代码点值，然后进行比较。因此，utf8mb4_bin 和 utf16_bin 的排序是相同的。这与 SQL:2008 标准对 UCS_BASIC 排序规则的要求一致：“UCS_BASIC 是一种排序规则，其中排序完全由被排序字符串中的字符的 Unicode 标量值决定。它适用于 UCS 字符集。由于每个字符集都是 UCS 字符集的子集，因此 UCS_BASIC 排序规则可能适用于每种字符集。注 11：字符的 Unicode 标量值是将其代码点视为无符号整数。”So MySQL's utf16_bin collation is not “byte by byte.” It is “by code point.” When MySQL sees a supplementary-character encoding in utf16, it converts to the character's code-point value, and then compares. Therefore, utf8mb4_bin and utf16_bin are the same ordering. This is consistent with the SQL:2008 standard requirement for a UCS_BASIC collation: “UCS_BASIC is a collation in which the ordering is determined entirely by the Unicode scalar values of the characters in the strings being sorted. It is applicable to the UCS character repertoire. Since every character repertoire is a subset of the UCS repertoire, the UCS_BASIC collation is potentially applicable to every character set. NOTE 11: The Unicode scalar value of a character is its code point treated as an unsigned integer.”

如果字符集是 ucs2，比较是逐字节进行的，但 ucs2 字符串无论如何都不应包含代理项。If the character set is ucs2, comparison is byte-by-byte, but ucs2 strings should not contain surrogates, anyway.

xxx_general_mysql500_ci 校对规则保留了原始 xxx_general_ci 校对规则在 5.1.24 之前的排序，并允许对 MySQL 5.1.24 之前创建的表进行升级（Bug #27877）。The xxx_general_mysql500_ci collations preserve the pre-5.1.24 ordering of the original xxx_general_ci collations and permit upgrades for tables created before MySQL 5.1.24 (Bug #27877).