Detaillierte Erläuterung der Rolle von COLLATE in MYSQL und der Unterschiede zwischen verschiedenen COLLATEs

Was ist COLLATE in MYSQL?

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 heute gehen wir los um den Schlüssel zu COLLATE Character zu besprechen. Was bedeutet der diesem Wert entsprechende utf8_unicode_ci? Wenn Sie diese Frage verwenden, um während des Vorstellungsgesprächs den DBA-Test zu absolvieren, 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:

phpmyadmin-Screenshot

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 Auswirkung 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 COLLATE mit dem Suffix _cs die Groß- und Kleinschreibung beachtet.

Verwenden Sie den Befehl show collation in MySQL, um alle von MySQL unterstützten COLLATEs anzuzeigen. Am Beispiel von utf8mb4 sind alle von dieser Codierung unterstützten COLLATEs wie in der folgenden Abbildung dargestellt.

Alle SORTIERUNGEN im Zusammenhang mit utf8mb4 in MySQL

Im Bild sehen wir die Sortierregeln der Sprachen vieler Länder. Die drei in China am häufigsten verwendeten sind utf8mb4_general_ci (Standard), utf8mb4_unicode_ci und utf8mb4_bin. Lassen Sie uns die Unterschiede zwischen diesen drei untersuchen:

Zunächst besteht die Vergleichsmethode von utf8mb4_bin darin, alle Zeichen direkt als Binärzeichenfolgen zu behandeln und sie dann vom höchsten zum niedrigsten Bit zu vergleichen. Es ist also offensichtlich, dass die Groß-/Kleinschreibung beachtet wird.

Es gibt eigentlich keinen Unterschied zwischen utf8mb4_unicode_ci und utf8mb4_general_ci für Chinesisch und Englisch. Für das System, das wir für den Hausgebrauch entwickelt haben, können Sie jedes beliebige auswählen. Es ist nur so, dass utf8mb4_unicode_ci für die Buchstaben einiger westlicher Länder eher ihren Sprachgewohnheiten entspricht als utf8mb4_general_ci. Allgemein ist ein älterer Standard von MySQL. Beispielsweise entspricht der deutsche Buchstabe „ß“ den beiden Buchstaben „ss“ in utf8mb4_unicode_ci (dies entspricht den deutschen Gewohnheiten), in utf8mb4_general_ci entspricht er jedoch dem Buchstaben „s“. Allerdings sind die subtilen Unterschiede zwischen den beiden Kodierungen für die normale Entwicklung schwer zu erkennen. Wir verwenden Textfelder selten zum direkten Sortieren, selbst wenn ein oder zwei Buchstaben falsch ausgerichtet sind. Kann das wirklich katastrophale Folgen für das System haben? Aus verschiedenen Beiträgen und Diskussionen im Internet geht hervor, dass immer mehr Leute die Verwendung von utf8mb4_unicode_ci empfehlen, aber sie sind nicht sehr resistent gegenüber Systemen, die den Standardwert verwenden, und glauben nicht, dass es ein großes Problem gibt. Fazit: Es wird empfohlen, utf8mb4_unicode_ci zu verwenden. Für Systeme, die bereits utf8mb4_general_ci verwenden, ist kein Zeitaufwand für die Änderung erforderlich.

Eine weitere zu beachtende Sache ist, dass ab MySQL 8.0 der Standard-CHARSET von MySQL nicht mehr Latin1 ist, sondern in utf8mb4 (Referenzlink) geändert wurde und der Standard-COLLATE ebenfalls in utf8mb4_0900_ai_ci geändert wurde. utf8mb4_0900_ai_ci ist im Allgemeinen eine weitere Unterteilung von Unicode. 0900 bezieht sich auf die Nummer des Unicode-Vergleichsalgorithmus (Version des Unicode-Sortierungsalgorithmus), und ai bedeutet akzentunabhängig (Aussprache ist irrelevant). gleich behandelt. Zugehöriger Referenzlink 1, zugehöriger Referenzlink 2

COLLATE-Einstellungsebene und ihre Priorität

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

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

CREATE DATABASE <db_name> 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的查询中也应该尽量避免使用中文做查询条件。

推荐学习：《mysql视频教程》

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung der Rolle von COLLATE in MYSQL und der Unterschiede zwischen verschiedenen COLLATEs. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!