MySQL :: MySQL 8.4 参考手册 :: 12.8.6 校对效果示例

8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

打开官方英文原文 ↗

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 12 种字符集、排序规则、Unicode / 12.8 整理问题 / 12.8.6 校对效果示例

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 79%

12.8.6 排序规则效果示例12.8.6 Examples of the Effect of Collation

示例 1：排序德语变音符号字母Example 1: Sorting German Umlauts

假设表 T 中的列 X 有这些 latin1 列值：Suppose that column X in table T has these latin1 column values:

Muffler
Müller
MX Systems
MySQL

假设列值是使用以下语句检索的：Suppose also that the column values are retrieved using the following statement:

SELECT X FROM T ORDER BY X COLLATE collation_name;

下表显示了如果我们使用ORDER BY并使用不同的排序规则时值的结果顺序。The following table shows the resulting order of the values if we use ORDER BY with different collations.

`latin1_swedish_ci`	`latin1_german1_ci`	`latin1_german2_ci`
消声器Muffler	消声器Muffler	穆勒Müller
MX 系统MX Systems	穆勒Müller	消声器Muffler
穆勒Müller	MX 系统MX Systems	MX 系统MX Systems
MySQL	MySQL	MySQL

在这个示例中导致不同排序顺序的字符是 ü（德语 “U-umlaut”）。The character that causes the different sort orders in this example is ü (German “U-umlaut”).

第一列显示使用瑞典/芬兰排序规则的 SELECT 结果，该规则规定 U-umlaut 与 Y 排序在一起。The first column shows the result of the SELECT using the Swedish/Finnish collating rule, which says that U-umlaut sorts with Y.
第二列显示使用德国 DIN-1 规则的 SELECT 结果，该规则规定 U-umlaut 与 U 排序在一起。The second column shows the result of the SELECT using the German DIN-1 rule, which says that U-umlaut sorts with U.
第三列显示使用德国 DIN-2 规则的 SELECT 结果，该规则规定 U-umlaut 与 UE 排序在一起。The third column shows the result of the SELECT using the German DIN-2 rule, which says that U-umlaut sorts with UE.

示例 2：搜索德语变音字母Example 2: Searching for German Umlauts

假设你有三个表，它们只在使用的字符集和排序规则上有所不同：Suppose that you have three tables that differ only by the character set and collation used:

mysql> SET NAMES utf8mb4;
mysql> CREATE TABLE german1 (
         c CHAR(10)
       ) CHARACTER SET latin1 COLLATE latin1_german1_ci;
mysql> CREATE TABLE german2 (
         c CHAR(10)
       ) CHARACTER SET latin1 COLLATE latin1_german2_ci;
mysql> CREATE TABLE germanutf8 (
         c CHAR(10)
       ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

每个表包含两条记录：Each table contains two records:

mysql> INSERT INTO german1 VALUES ('Bar'), ('Bär');
mysql> INSERT INTO german2 VALUES ('Bar'), ('Bär');
mysql> INSERT INTO germanutf8 VALUES ('Bar'), ('Bär');

上述三种排序规则中有两种存在 A = Ä 的相等性，而有一种不存在这种相等性（latin1_german2_ci）。因此，比较会产生如下所示的结果：Two of the above collations have an A = Ä equality, and one has no such equality (latin1_german2_ci). For that reason, comparisons yield the results shown here:

mysql> SELECT * FROM german1 WHERE c = 'Bär';
+------+
| c    |
+------+
| Bar  |
| Bär  |
+------+
mysql> SELECT * FROM german2 WHERE c = 'Bär';
+------+
| c    |
+------+
| Bär  |
+------+
mysql> SELECT * FROM germanutf8 WHERE c = 'Bär';
+------+
| c    |
+------+
| Bar  |
| Bär  |
+------+

这不是一个错误，而是 latin1_german1_ci 和 utf8mb4_unicode_ci 排序属性的结果（所示排序是根据德国 DIN 5007 标准进行的）。This is not a bug but rather a consequence of the sorting properties of latin1_german1_ci and utf8mb4_unicode_ci (the sorting shown is done according to the German DIN 5007 standard).

首页上一级下一节