Was bedeutet Sortieren in MySQL?

MySQLs Sortierung bedeutet einen Sortiersatz, der als Sortierregel verstanden werden kann. In MySQL können gespeicherte Zeichendaten abhängig vom verwendeten Zeichensatz und den verwendeten Sortierregeln unterschiedliche Sortierungen haben. Der Zeichensatz bestimmt, welche Zeichentypen gespeichert werden können, und die Sortierregeln bestimmen, wie diese Zeichen sortiert werden.

Das Betriebssystem dieses Tutorials: Windows 10-System, MySQL-Version 8.0, Dell G3-Computer.

mysql COLLATE bedeutet Kollationssatz, der als Sortierregel usw. verstanden werden kann.

In MySQL können gespeicherte Zeichendaten je nach verwendetem Zeichensatz und Sortierregeln unterschiedliche Sortierungen haben. Der Zeichensatz bestimmt, welche Zeichentypen gespeichert werden können, und die Sortierregeln bestimmen, wie diese Zeichen sortiert werden.

Normalerweise können Sie beim Erstellen einer Datenbank oder Tabelle die Sortiermethode für Zeichendaten festlegen, indem Sie verschiedene Zeichensätze und Sortierregeln angeben. Wenn keine spezifische Sortierregel angegeben ist, verwendet MySQL die Standard-Sortierungsregel. Zu den gängigen Zeichensätzen und Sortierregeln gehören utf8mb4_general_ci (ohne Berücksichtigung der Groß- und Kleinschreibung, ohne Berücksichtigung des Akzents), utf8mb4_unicode_ci (ohne Berücksichtigung der Groß- und Kleinschreibung, ohne Berücksichtigung des Akzents) usw.

Die Auswahl der Sortierregeln richtet sich nach den spezifischen Anforderungen. Für unterschiedliche Szenarien eignen sich unterschiedliche Sortierregeln. Beim Suchen und Sortieren von Daten führen einige Sortierregeln beispielsweise Vergleiche unter Berücksichtigung der Groß- und Kleinschreibung durch, andere dagegen nicht. Daher sollten beim Entwerfen einer Datenbank geeignete Sortierregeln basierend auf den tatsächlichen Anforderungen und Geschäftsregeln ausgewählt werden.

Verschiedene Sortiermethoden können durch die Verwendung unterschiedlicher Sortierregeln erreicht werden. Beispielsweise können Zeichendaten ohne Berücksichtigung der Groß-/Kleinschreibung sortiert werden, was bedeutet, dass „A“ und „a“ als gleich betrachtet werden. Im Gegensatz dazu würden „A“ und „a“ als unterschiedliche Zeichen behandelt, wenn bei der Sortierung die Groß-/Kleinschreibung beachtet würde.

Darüber hinaus können sich Sortierregeln auch auf String-Vergleichsvorgänge auswirken. Bei unterschiedlichen Sortierregeln können die Vergleichsergebnisse zwischen Zeichen unterschiedlich sein. Beispielsweise kann nach einigen Sortierregeln der Buchstabe „a“ als größer als der Buchstabe „Z“ angesehen werden, während nach anderen Regeln das Gegenteil der Fall ist.

Es ist wichtig zu beachten, dass die Sortierregeln nicht nur für das Sortieren und Vergleichen von Zeichendaten gelten, sondern auch für die Ergebnisse, wenn Zeichenfolgenfunktionen und -operatoren in Abfragen verwendet werden. Daher sollten Sie beim Schreiben von SQL-Abfragen die verwendeten Sortierregeln berücksichtigen, um sicherzustellen, dass Sie die erwarteten Ergebnisse erhalten.

Führen Sie den Befehl show create table aus, und Sie können die Tabellenerstellungsanweisung einer Tabelle wie folgt sehen:

CREATE TABLE `table1` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `field1` text COLLATE utf8_unicode_ci NOT NULL COMMENT '字段1',
    `field2` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '字段2',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_unicode_ci;

Wir können die meisten Felder verstehen, aber was wir heute besprechen möchten das COLLATE-Schlüsselwort. Was bedeutet der diesem Wert entsprechende utf8_unicode_ci? Wenn Sie diese Frage verwenden, um die DBA-Prüfung während des Vorstellungsgesprächs abzulegen, dürfte dies die meisten Menschen aus der Fassung bringen.

Wofür wird COLLATE verwendet?

Entwickler, die phpmyadmin verwenden, kommen Ihnen vielleicht sehr bekannt vor, denn der chinesische Header hat bereits die Antwort gegeben:

Der sogenannte utf8_unicode_ci ist eigentlich eine Regel, die zum Sortieren verwendet wird. Für diese Zeichentypspalten in MySQL, wie z. B. Spalten vom Typ VARCHAR, CHAR und TEXT, ist ein COLLATE-Typ erforderlich, um MySQL mitzuteilen, wie die Spalten sortiert und verglichen werden sollen. Kurz gesagt, COLLATE beeinflusst die Reihenfolge der ORDER BY-Anweisung, die durch das Größer- oder Kleiner-Zeichen in der WHERE-Bedingung herausgefilterten Ergebnisse sowie **DISTINCT**, **GROUP BY** und **HAVING* * Anweisungen. Abfrageergebnisse. Wenn MySQL außerdem einen Index erstellt und die Indexspalte vom Typ Zeichen ist, wirkt sich dies auch auf die Indexerstellung aus, wir können diese Auswirkungen jedoch nicht wahrnehmen. Kurz gesagt: Wo immer es um den Vergleich oder die Sortierung von Zeichentypen geht, hängt dies mit COLLATE zusammen.

Der Unterschied zwischen verschiedenen COLLATEs

COLLATE hängt normalerweise mit der Datenkodierung (CHARSET) zusammen. Im Allgemeinen unterstützt jedes CHARSET mehrere COLLATEs, und jedes CHARSET gibt ein COLLATE als Standardwert an. Beispielsweise ist die Standard-COLLATE für die Codierung „Latin1“ latin1_swedish_ci, die Standard-COLLATE für die Codierung „GBK“ ist gbk_chinese_ci und der Standardwert für die Codierung „utf8mb4“ ist utf8mb4_general_ci.

Lassen Sie mich übrigens einen Exkurs machen. Es gibt zwei Kodierungen in MySQL: utf8 und utf8mb4. Vergessen Sie in MySQL bitte **utf8** und verwenden Sie immer **utf8mb4**. Dies ist ein Legacy-Problem von MySQL. MySQL unterstützt nur Zeichenkodierungen mit einer maximalen Länge von 3 Bytes. Für einige Texte, die 4 Bytes belegen müssen, muss utf8mb4 verwendet werden.

Viele COLLATEs haben das Wort _ci, die Abkürzung für Case Insensitive, was bedeutet, dass „A“ und „a“ beim Sortieren und Vergleichen gleich behandelt werden. Auswahl * aus Tabelle1, wobei Feld1="a" auch den Wert von Feld1 als „A“ auswählen kann. Gleichzeitig wird bei COLLATEs mit dem Suffix _cs die Groß- und Kleinschreibung beachtet.

在mysql中使用show collation指令可以查看到mysql所支持的所有COLLATE。以utf8mb4为例，该编码所支持的所有COLLATE如下图所示。

imgmysql中和utf8mb4相关的所有COLLATE

图中我们能看到很多国家的语言自己的排序规则。在国内比较常用的是utf8mb4_general_ci（默认）、utf8mb4_unicode_ci、utf8mb4_bin这三个。我们来探究一下这三个的区别：

首先utf8mb4_bin的比较方法其实就是直接将所有字符看作二进制串，然后从最高位往最低位比对。所以很显然它是区分大小写的。

而utf8mb4_unicode_ci和utf8mb4_general_ci对于中文和英文来说，其实是没有任何区别的。对于我们开发的国内使用的系统来说，随便选哪个都行。只是对于某些西方国家的字母来说，utf8mb4_unicode_ci会比utf8mb4_general_ci更符合他们的语言习惯一些，general是mysql一个比较老的标准了。例如，德语字母“ß”，在utf8mb4_unicode_ci中是等价于"ss"两个字母的（这是符合德国人习惯的做法），而在utf8mb4_general_ci中，它却和字母“s”等价。不过，这两种编码的那些微小的区别，对于正常的开发来说，很难感知到。本身我们也很少直接用文字字段去排序，退一步说，即使这个字母排错了一两个，真的能给系统带来灾难性后果么？从网上找的各种帖子讨论来说，更多人推荐使用utf8mb4_unicode_ci，但是对于使用了默认值的系统，也并没有非常排斥，并不认为有什么大问题。结论：推荐使用utf8mb4_unicode_ci，对于已经用了utf8mb4_general_ci的系统，也没有必要花时间改造。

另外需要注意的一点是，从mysql 8.0开始，mysql默认的CHARSET已经不再是Latin1了，改为了utf8mb4（参考链接），并且默认的COLLATE也改为了utf8mb4_0900_ai_ci。utf8mb4_0900_ai_ci大体上就是unicode的进一步细分，0900指代unicode比较算法的编号（ Unicode Collation Algorithm version），ai表示accent insensitive（发音无关），例如e, è, é, ê 和 ë是一视同仁的。相关参考链接1，相关参考链接2

COLLATE设置级别及其优先级

设置COLLATE可以在示例级别、库级别、表级别、列级别、以及SQL指定。实例级别的COLLATE设置就是mysql配置文件或启动指令中的collation_connection系统变量。

库级别设置COLLATE的语句如下：

CREATE DATABASE DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
如果库级别没有设置CHARSET和COLLATE，则库级别默认的CHARSET和COLLATE使用实例级别的设置。在mysql8.0以下版本中，你如果什么都不修改，默认的CHARSET是Latin1，默认的COLLATE是latin1_swedish_ci。从mysql8.0开始，默认的CHARSET已经改为了utf8mb4，默认的COLLATE改为了utf8mb4_0900_ai_ci。

表级别的COLLATE设置，则是在CREATE TABLE的时候加上相关设置语句，例如：

CREATE TABLE (
 
……
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

如果表级别没有设置CHARSET和COLLATE，则表级别会继承库级别的CHARSET与COLLATE。

列级别的设置，则在CREATE TABLE中声明列的时候指定，例如  

CREATE TABLE (
 
`field1` VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
 
……
 
) ……

如果列级别没有设置CHARSET和COLATE，则列级别会继承表级别的CHARSET与COLLATE。

最后，你也可以在写SQL查询的时候显示声明COLLATE来覆盖任何库表列的COLLATE设置，不太常用，了解即可：

SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;
 
SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;

如果全都显示设置了，那么优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。也就是说列上所指定的COLLATE可以覆盖表上指定的COLLATE，表上指定的COLLATE可以覆盖库级别的COLLATE。如果没有指定，则继承下一级的设置。即列上面没有指定COLLATE，则该列的COLLATE和表上设置的一样。

以上就是关于mysql的COLLATE相关知识。不过，在系统设计中，我们还是要尽量避免让系统严重依赖中文字段的排序结果，在mysql的查询中也应该尽量避免使用中文做查询条件。

Das obige ist der detaillierte Inhalt vonWas bedeutet Sortieren in MySQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!