Was ist COLLATE in MYSQL?

Vorwort

Führen Sie den Befehl show create table <tablename> in MySQL aus, um die Anweisung zur Tabellenerstellung für eine Tabelle anzuzeigen. Das Beispiel lautet wie folgt:

CREATE TABLE `table1` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `field1` Text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Feld 1',
    `field2` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Feld 2',
    PRIMÄRSCHLÜSSEL (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_unicode_ci;

Die meisten Felder sind für uns verständlich, heute besprechen wir jedoch das Schlüsselwort COLLATE. Was bedeutet das utf8_unicode_ci, das diesem Wert entspricht? Die Verwendung dieser Frage, um DBAs während Vorstellungsgesprächen zu testen, wird die meisten Leute wahrscheinlich in Verlegenheit bringen.

Wofür wird COLLATE verwendet?

Die Entwicklung mit phpmyadmin kommt Ihnen vielleicht bekannt vor, denn die chinesische Überschrift gibt die Antwort:

Das sogenannte utf8_unicode_ci ist eigentlich eine Regel, die zum Sortieren verwendet wird. Für Spalten vom Zeichentyp in MySQL, beispielsweise Spalten vom Typ VARCHAR, CHAR und TEXT, ist ein COLLATE-Typ erforderlich, um MySQL mitzuteilen, wie die Spalte sortiert und verglichen werden soll. Kurz gesagt beeinflusst COLLATE die Reihenfolge der ORDER BY-Anweisung, beeinflusst die Ergebnisse, die durch das Größer-als- oder Kleiner-als-Zeichen in der WHERE-Bedingung herausgefiltert werden , und beeinflusst die Abfrageergebnisse der Anweisungen DISTINCT, GROUP BY und HAVING . Darüber hinaus wirkt sich eine Indexerstellung durch MySQL auch dann aus, wenn die Indexspalte vom Zeichentyp ist. Diese Auswirkung können wir jedoch nicht erkennen. Kurz gesagt: Wo immer ein Vergleich oder eine Sortierung von Zeichentypen erforderlich ist, wird dies mit COLLATE in Zusammenhang gebracht .

Der Unterschied zwischen verschiedenen COLLATE

COLLATE bezieht sich normalerweise auf die Datencodierung (CHARSET). Im Allgemeinen unterstützt jedes CHARSET mehrere COLLATEs und jedes CHARSET gibt ein COLLATE als Standardwert an. Beispielsweise lautet das Standard-COLLATE für die Latin1-Kodierung „latin1_swedish_ci“, das Standard-COLLATE für die GBK-Kodierung „gbk_chinese_ci“ und das Standard-COLLATE für die utf8mb4-Kodierung „utf8mb4_general_ci“.

Übrigens gibt es in MySQL zwei Kodierungen: utf8 und utf8mb4. Vergessen Sie in MySQL bitte **utf8** und verwenden Sie immer **utf8mb4**. Dies ist ein veraltetes Problem von MySQL. Das UTF-8 in MySQL unterstützt nur Zeichenkodierungen mit einer maximalen Länge von 3 Byte. Für manchen Text, der 4 Bytes belegen muss, unterstützt MySQLs UTF-8 dies nicht, und es muss UTF-8MB4 verwendet werden.

Viele COLLATEs enthalten das Wort _ci, die Abkürzung für Case Insensitive (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 gilt für die COLLATEs mit dem Suffix _cs: Case Sensitive, d. h., es wird zwischen Groß- und Kleinschreibung unterschieden.

Verwenden Sie den Befehl „show collation“ in MySQL, um alle von MySQL unterstützten COLLATEs anzuzeigen. Am Beispiel von utf8mb4 werden in der folgenden Abbildung alle von dieser Kodierung unterstützten COLLATEs angezeigt.

Alle COLLATE im Zusammenhang mit utf8mb4 in imgmysql

Im Bild sehen wir die Sortierregeln der Sprachen vieler Länder. Die drei im Land 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 bis zum niedrigsten Bit zu vergleichen. Es muss also offensichtlich zwischen Groß- und Kleinschreibung unterschieden werden.

Es gibt eigentlich keinen Unterschied zwischen utf8mb4_unicode_ci und utf8mb4_general_ci für Chinesisch und Englisch. Für die Systeme, die wir für den Hausgebrauch entwickeln, ist jedes davon geeignet. Es ist nur so, dass für die Buchstaben einiger westlicher Länder utf8mb4_unicode_ci eher ihren Sprachgewohnheiten entspricht als utf8mb4_general_ci. General ist ein älterer MySQL-Standard. Beispielsweise entspricht der deutsche Buchstabe „ß“ in utf8mb4_unicode_ci den beiden Buchstaben „ss“ (das entspricht der deutschen Gepflogenheit), in utf8mb4_general_ci jedoch dem Buchstaben „s“. Bei einer normalen Entwicklung sind die subtilen Unterschiede zwischen den beiden Kodierungen jedoch schwer wahrzunehmen. Wir verwenden Textfelder selten direkt zum Sortieren. Selbst wenn ein oder zwei Buchstaben falsch sortiert sind, kann das wirklich katastrophale Folgen für das System haben? Aus verschiedenen Beiträgen und Diskussionen im Internet geht hervor, dass die meisten Leute die Verwendung von utf8mb4_unicode_ci empfehlen, aber keine großen Einwände gegen Systeme haben, die die Standardwerte verwenden, und ihrer Meinung nach keine großen Probleme verursachen. Fazit: Es wird empfohlen, utf8mb4_unicode_ci zu verwenden. Bei Systemen, die bereits utf8mb4_general_ci verwendet haben, ist es nicht notwendig, Zeit mit deren Änderung zu verbringen.

Ein weiterer zu beachtender Punkt ist, dass ab MySQL 8.0 der Standard-CHARSET von MySQL nicht mehr Latin1 ist, sondern in utf8mb4 (Referenzlink) geändert wurde und dass auch das Standard-COLLATE in utf8mb4_0900_ai_ci geändert wurde. utf8mb4_0900_ai_ci ist grundsätzlich eine weitere Unterteilung von Unicode, 0900 bezieht sich auf die Version des Unicode-Sortieralgorithmus und ai bedeutet „accent insensitiv“ (ohne Berücksichtigung der Aussprache), beispielsweise werden e, è, é, ê und ë gleich behandelt. Verwandter Referenzlink 1, Verwandter Referenzlink 2

COLLATE-Einstellungsebenen und ihre Priorität

COLLATE-Einstellungen können auf Instanzebene, Bibliotheksebene, Tabellenebene, Spaltenebene und SQL-Ebene angegeben werden. Die COLLATE-Einstellung auf Instanzebene ist die Systemvariable collation_connection in der MySQL-Konfigurationsdatei oder im Startbefehl.

Die Anweisung zum Festlegen von COLLATE auf Bibliotheksebene lautet wie folgt:

DATENBANK ERSTELLEN <db_name> STANDARDZEICHENSATZ utf8mb4 COLLATE utf8mb4_unicode_ci;
Wenn CHARSET und COLLATE nicht auf Bibliotheksebene festgelegt sind, verwenden die Standardeinstellungen CHARSET und COLLATE auf Bibliotheksebene die Einstellungen auf Instanzebene. Wenn Sie in MySQL-Versionen unter 8.0 nichts ändern, ist der Standard-CHARSET Latin1 und der Standard-COLLATE latin1_swedish_ci. Ab MySQL 8.0 wurde das Standard-CHARSET in utf8mb4 und das Standard-COLLATE in utf8mb4_0900_ai_ci geändert.

Mit der COLLATE-Einstellung auf Tabellenebene können Sie beim Erstellen einer Tabelle relevante Einstellungsanweisungen hinzufügen, zum Beispiel:

TABELLE ERSTELLEN (

…

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Wenn CHARSET und COLLATE nicht auf Tabellenebene festgelegt sind, erbt die Tabellenebene CHARSET und COLLATE auf Datenbankebene.

Einstellungen auf Spaltenebene werden beim Deklarieren der Spalte in CREATE TABLE angegeben, zum Beispiel

TABELLE ERSTELLEN (

`field1` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',

…

) ……

Wenn CHARSET und COLATE nicht auf Spaltenebene festgelegt sind, erbt die Spaltenebene CHARSET und COLLATE auf Tabellenebene.

Schließlich können Sie COLLATE auch explizit deklarieren, wenn Sie SQL-Abfragen schreiben, um die COLLATE-Einstellung einer beliebigen Datenbanktabellenspalte zu überschreiben. Dies wird nicht häufig verwendet, ist aber leicht zu verstehen:

Wählen Sie ein unterschiedliches Feld1 aus und sortieren Sie utf8mb4_general_ci aus Tabelle1.

SELECT Feld1, Feld2 FROM Tabelle1 ORDER BY Feld1 COLLATE utf8mb4_unicode_ci;

Wenn alle Einstellungen angezeigt werden, lautet die Prioritätsreihenfolge SQL-Anweisung > Einstellungen auf Spaltenebene > Einstellungen auf Tabellenebene > Einstellungen auf Bibliotheksebene > Einstellungen auf Instanzebene. Das bedeutet, dass das für die Spalte angegebene COLLATE das für die Tabelle angegebene COLLATE überschreiben kann und dass das für die Tabelle angegebene COLLATE das COLLATE auf Bibliotheksebene überschreiben kann. Wenn nicht angegeben, wird die Einstellung von der nächsten Ebene übernommen. Das heißt, wenn COLLATE für die Spalte nicht angegeben ist, ist COLLATE für die Spalte identisch mit dem für die Tabelle festgelegten Wert.

Das Obige ist das Wissen über COLLATE von MySQL. Allerdings sollten wir beim Systemdesign vermeiden, dass das System stark von den Sortierergebnissen chinesischer Felder abhängig wird, und wir sollten auch versuchen, die Verwendung von Chinesisch als Abfragebedingungen in MySQL-Abfragen zu vermeiden.

Zusammenfassen

Dies ist das Ende dieses Artikels über COLLATE in MYSQL. Weitere Informationen zu MYSQL COLLATE finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!