Detaillierte Erklärung von COLLATION-Beispielen in MySQL, die Sie möglicherweise übersehen haben

Vorwort

Die Zeichenfolgentypen der MySQL-Datenbank sind CHAR, VARCHAR, BINARY, BLOB, TEXT, ENUM und SET. Verschiedene Typen weisen hinsichtlich Geschäftsdesign und Datenbankleistung völlig unterschiedliche Leistungen auf. CHAR und VARCHAR werden am häufigsten verwendet. Heute werde ich Ihnen mehr über die Anwendung der Zeichenfolgentypen CHAR und VARCHAR beibringen.

Definition von CHAR und VARCHAR

CHAR(N) wird zum Speichern von Zeichen fester Länge verwendet. Der Bereich von N reicht von 0 bis 255. Bitte beachten Sie, dass N Zeichen und nicht Bytes darstellt. VARCHAR(N) wird zum Speichern von Zeichen variabler Länge verwendet. Der Bereich von N reicht von 0 bis 65536, und N stellt auch Zeichen dar.

Wenn die Größe 65536 Bytes überschreitet, können Sie einen größeren Zeichentyp verwenden, TEXT oder BLOB. Beide haben eine maximale Speicherlänge von 4G. Der Unterschied besteht darin, dass BLOB kein Zeichensatzattribut hat und rein binärer Speicher ist.

Im Gegensatz zu herkömmlichen relationalen Datenbanken wie Oracle und SQL Server kann der VARCHAR-Zeichentyp der MySQL-Datenbank maximal 65536 Byte speichern. Daher ist der VARCHAR-Typ in der MySQL-Datenbank in den meisten Szenarien ausreichend.

Zeichensatz

Beim Entwurf der Tabellenstruktur müssen neben der Definition der Spalten als CHAR und VARCHAR zum Speichern von Zeichen auch die den Zeichen entsprechenden Zeichensätze definiert werden, da jedes Zeichen unter unterschiedlichen Zeichensatzcodierungen einem anderen Binärwert entspricht. Zu den gängigen Zeichensätzen gehören GBK und UTF8. Normalerweise wird empfohlen, den Standardzeichensatz auf UTF8 festzulegen.

Und angesichts der rasanten Entwicklung des mobilen Internets wird empfohlen, den Standardzeichensatz von MySQL auf UTF8MB4 einzustellen. Andernfalls können einige Emoji-Zeichen nicht im UTF8-Zeichensatz gespeichert werden. Beispielsweise hat das Emoji-Smiley-Gesicht eine entsprechende Zeichenkodierung von 0xF09F988E:

Wenn Sie Emoji-Zeichen zwangsweise in eine Spalte einfügen, deren Zeichensatz UTF8 ist, gibt MySQL die folgende Fehlermeldung aus:

mysql> ANZEIGEN ERSTELLEN TABELLE emoji_test\G

*************************** 1. Reihe ***************************

       Tabelle: emoji_test

Tabelle erstellen: CREATE TABLE `emoji_test` (

  `a` varchar(100) Zeichensatz utf8,

  PRIMÄRSCHLÜSSEL (`a`)

) ENGINE=InnoDB STANDARD-CHARSET=utf8



1 Zeile im Satz (0,01 Sek.)

mysql> EINFÜGEN IN emoji_test WERTE (0xF09F988E);

FEHLER 1366 (HY000): Falscher Zeichenfolgenwert: '\xF0\x9F\x98\x8E' für Spalte 'a' in Zeile 1

Ab MySQL Version 8.0 ist der Zeichensatz standardmäßig auf UTF8MB4 eingestellt. Vor Version 8.0 war der Standardzeichensatz Latin1. Da die Standardzeichensätze verschiedener Versionen unterschiedlich sind, müssen Sie die relevanten Parameter in der Konfigurationsdatei explizit konfigurieren:

[mysqld]

Zeichensatzserver = utf8mb4

...

Darüber hinaus weisen unterschiedliche Zeichensätze unterschiedliche entsprechende längste Bytes für CHAR(N) und VARCHAR(N) auf. Beispielsweise kann im GBK-Zeichensatz ein Zeichen in maximal 2 Bytes gespeichert werden, und im UTF8MB4-Zeichensatz kann ein Zeichen in maximal 4 Bytes gespeichert werden. Aus der Perspektive des zugrunde liegenden Speicherkernels sind die zugrunde liegenden Implementierungen von CHAR und VARCHAR bei einem Mehrbyte-Zeichensatz also genau gleich, beide sind Speicher mit variabler Länge!

Aus dem obigen Beispiel können wir erkennen, dass CHAR(1) sowohl 1 Byte „a“ als auch 4 Byte Emoji-Smiley speichern kann, CHAR ist also ebenfalls von Natur aus von variabler Länge.

Da der aktuell empfohlene Standardzeichensatz UTF8MB4 ist, können Sie beim Entwerfen der Tabellenstruktur alle CHAR durch VARCHAR ersetzen und der zugrunde liegende Speicher ist im Wesentlichen derselbe.

Sortierregeln

Die Sortierung ist eine Regel zum Vergleichen und Sortieren von Zeichenketten. Jeder Zeichensatz hat eine Standardsortierung, die Sie mit dem Befehl SHOW CHARSET anzeigen können:

mysql> ZEIGE CHARSET WIE 'utf8%';

+---------+------------------+--------------------+--------+

| Zeichensatz | Beschreibung | Standardsortierung | Maxlen |

+---------+------------------+--------------------+--------+

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |

+---------+------------------+--------------------+--------+

2 Zeilen im Satz (0,01 Sek.)



mysql> SORTIMENTIERUNG ANZEIGEN WIE 'utf8mb4%';

+----------------------------+------+-----+---------+----------+----------+---------+-----------+

| Sortierung | Zeichensatz | ID | Standard | Kompiliert | Sortlen | Pad_attribute |

+----------------------------+------+-----+---------+----------+----------+---------+-----------+

| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Ja | Ja | 0 | KEIN PAD |

| utf8mb4_0900_as_ci | utf8mb4 | 305 | | Ja | 0 | KEIN PAD |

| utf8mb4_0900_as_cs | utf8mb4 | 278 | | Ja | 0 | KEIN PAD |

| utf8mb4_0900_bin | utf8mb4 | 309 | | Ja | 1 | KEIN PAD |

| utf8mb4_bin | utf8mb4 | 46 | | Ja | 1 | PAD SPACE |

......

Die Sortierregeln enden mit _ci, was „Groß-/Kleinschreibung ignorieren“ bedeutet, _cs, was „Groß-/Kleinschreibung beachten“ bedeutet, und _bin, was „Vergleich durch Speichern der Binärdarstellung der Zeichen“ bedeutet. Es ist zu beachten, dass beim Vergleichen von MySQL-Zeichenfolgen die Groß-/Kleinschreibung bei der Standardsortierung nicht beachtet wird:

mysql> AUSWÄHLEN 'a' = 'A';

+-------------+

| "ein" = "Ein" |

+-------------+

| 1 |

+-------------+

1 Zeile im Satz (0,00 Sek.)



mysql> SELECT CAST('a' as char) COLLATE utf8mb4_0900_as_cs = CAST('A' as CHAR) COLLATE utf8mb4_0900_as_cs als Ergebnis;

+--------+

| Ergebnis |

+--------+

| 0 |

+--------+

1 Zeile im Satz (0,00 Sek.)

Bedenken Sie, dass die Tabellenstruktur in den meisten Fällen keine Groß-/Kleinschreibung der Sortierregeln erfordert! Es sei denn, Sie verstehen, was Ihr Unternehmen wirklich braucht.

Den Zeichensatz richtig ändern

Natürlich glaube ich, dass viele Unternehmen die Auswirkungen von Zeichensätzen auf die Speicherung von Geschäftsdaten beim Entwurf nicht berücksichtigen, sodass später eine Zeichensatzkonvertierung erforderlich ist. Viele Schüler werden jedoch feststellen, dass sie nach der Durchführung der folgenden Vorgänge immer noch keine UTF8MB4-Zeichen wie Emoji einfügen können:

ALTER TABLE emoji_test CHARSET utf8mb4;

Tatsächlich ändert die obige Änderung nur den Zeichensatz der Tabelle auf UTF8MB4. Wenn Sie das nächste Mal eine neue Spalte hinzufügen und den Zeichensatz nicht explizit angeben, wird der Zeichensatz der neuen Spalte auf UTF8MB4 geändert. Für vorhandene Spalten wird der Standardzeichensatz jedoch nicht geändert. Sie können dies bestätigen, indem Sie den Befehl SHOW CREATE TABLE ausführen:

mysql> ANZEIGEN TABELLE ERSTELLEN emoji_test\G

*************************** 1. Reihe ***************************

       Tabelle: emoji_test

Tabelle erstellen: CREATE TABLE `emoji_test` (

  `a` varchar (100) Zeichensatz utf8 COLLATE utf8_general_ci NICHT NULL,

  PRIMÄRSCHLÜSSEL (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 Zeile im Satz (0,00 Sek.)

Sie können sehen, dass der Zeichensatz der Spalte a immer noch UTF8 und nicht UTF8MB4 ist. Der richtige Befehl zum Ändern des Spaltenzeichensatzes müsste daher ALTER TABLE ... CONVERT TO ... lauten, damit der vorherige Spaltenzeichensatz von UTF8 in UTF8MB4 geändert werden kann:

mysql> ALTER TABLE emoji_test IN CHARSET KONVERTIEREN utf8mb4;

Abfrage OK, 0 Zeilen betroffen (0,94 Sek.)

Datensätze: 0 Duplikate: 0 Warnungen: 0



mysql> ANZEIGEN TABELLE ERSTELLEN emoji_test\G

*************************** 1. Reihe ***************************

       Tabelle: emoji_test

Tabelle erstellen: CREATE TABLE `emoji_test` (

  `a` varchar (100) Zeichensatz utf8mb4 Sortiert utf8mb4_0900_ai_ci nicht NULL,

  PRIMÄRSCHLÜSSEL (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 Zeile im Satz (0,00 Sek.)

Praktischer Entwurf einer Geschäftstabellenstruktur

Benutzergeschlechtsdesign

Beim Entwerfen einer Tabellenstruktur werden Sie auf einige Felder mit festen optionalen Werten stoßen. Beispielsweise kann das Feld „Geschlecht“ (Sex) nur die Angaben „männlich“ oder „weiblich“ enthalten; oder das Feld „Status“ (State) hat eine begrenzte Anzahl gültiger Werte, z. B. „wird ausgeführt“, „gestoppt“ und „neu gestartet“.
Mir ist aufgefallen, dass die meisten Entwickler zum Speichern des Felds „Geschlecht“ gerne den numerischen Typ INT verwenden, zum Beispiel:

CREATE TABLE `Benutzer` (

  `id` bigint NICHT NULL AUTO_INCREMENT,

  `Geschlecht` tinyint DEFAULT NULL,

  ......

  PRIMÄRSCHLÜSSEL (`id`)

)ENGINE=InnoDB;

Darunter gibt die Tinyint-Spalte „Sex“ das Geschlecht des Benutzers an, aber dieses Designproblem ist ziemlich offensichtlich.

• Unklarer Ausdruck: Steht beim Speichern von Daten 0 für weiblich oder steht 1 für weiblich? Jedes Unternehmen kann andere unausgesprochene Regeln haben;
• Schmutzige Daten: Da es sich um Tinyint handelt, können Benutzer neben 0 und 1 auch Werte wie 2, 3 und 4 einfügen. Am Ende besteht die Möglichkeit, dass die Tabelle ungültige Daten enthält und der Aufwand für die spätere Bereinigung sehr hoch ist.

Vor MySQL 8.0 konnten Sie den Zeichenfolgenaufzählungstyp ENUM verwenden, der nur das Einfügen einer begrenzten Anzahl definierter Werte erlaubte. Wenn der Parameter SQL_MODE auf den strikten Modus eingestellt ist, führt das Einfügen undefinierter Daten zu einem Fehler:

mysql> SHOW CREATE TABLE Benutzer\G

*************************** 1. Reihe ***************************

       Tabelle: Benutzer

Tabelle erstellen: CREATE TABLE `User` (

  `id` bigint NICHT NULL AUTO_INCREMENT,

  `Geschlecht` enum('M','F') COLLATE utf8mb4_general_ci DEFAULT NULL,

  PRIMÄRSCHLÜSSEL (`id`)

) ENGINE=InnoDB

1 Zeile im Satz (0,00 Sek.)



mysql> SETze sql_mode = "STRICT_TRANS_TABLES";

Abfrage OK, 0 Zeilen betroffen, 1 Warnung (0,00 Sek.)



mysql> INSERT INTO Benutzerwerte (NULL,'F');

Abfrage OK, 1 Zeile betroffen (0,08 Sek.)



mysql> INSERT INTO Benutzerwerte (NULL,'A');

FEHLER 1265 (01000): Daten für Spalte „Geschlecht“ in Zeile 1 abgeschnitten

Weil der ENUM-Typ kein SQL-Standarddatentyp ist, sondern ein für MySQL eindeutiger Zeichenfolgentyp. Auch die ausgegebene Fehlermeldung ist nicht intuitiv. Diese Implementierung ist immer etwas bedauerlich, vor allem, weil MySQL-Versionen vor 8.0 keine Constraint-Funktionalität bieten. Seit MySQL 8.0.16 stellt die Datenbank nativ die Einschränkungsfunktion CHECK bereit, die den Entwurf von Spaltentypen mit endlichen Zuständen erleichtern kann:

mysql> SHOW CREATE TABLE Benutzer\G

*************************** 1. Reihe ***************************

       Tabelle: Benutzer

Tabelle erstellen: CREATE TABLE `User` (

  `id` bigint NICHT NULL AUTO_INCREMENT,

  `sex` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL,

  Primärschlüssel (`id`),

  EINSCHRÄNKUNG `user_chk_1` PRÜFEN (((`sex` = _utf8mb4'M') oder (`sex` = _utf8mb4'F')))

) ENGINE=InnoDB

1 Zeile im Satz (0,00 Sek.)



mysql> INSERT INTO Benutzerwerte (NULL,'M');

Abfrage OK, 1 Zeile betroffen (0,07 Sek.)



mysql> INSERT INTO Benutzerwerte (NULL,'Z');

FEHLER 3819 (HY000): Die Prüfbedingung „user_chk_1“ ist verletzt.

Aus diesem Code können wir ersehen, dass die Einschränkungsdefinition user_chk_1 in Zeile 8 den Wertebereich der Spalte „Geschlecht“ angibt, der nur M oder F sein kann. Gleichzeitig können Sie sehen, dass MySQL beim Einfügen der unzulässigen Daten Z explizit eine Eingabeaufforderung bezüglich der unzulässigen Einschränkung ausgibt.

Design der Kontokennwortspeicherung

Denken Sie beim Entwurf der Datenbanktabellenstruktur daran, Passwörter niemals direkt in der Datenbanktabelle zu speichern. Sobald ein böswilliger Benutzer in das System eindringt, besteht ein großes Risiko, dass Benutzerdaten verloren gehen. Beispielsweise müssen in der Finanzbranche aus Compliance-Sicht alle Datenschutzfelder der Benutzer verschlüsselt werden, und nicht einmal das Unternehmen selbst kann die vom Benutzer gespeicherten Informationen (private Daten wie Anmeldekennwörter, Mobiltelefonnummern, Kreditkarteninformationen usw.) kennen.

Ich glaube, dass viele Entwickler die MD5-Funktion zum Verschlüsseln und Speichern privater Daten verwenden werden. Das ist richtig, da der MD5-Algorithmus nicht umkehrbar ist. Der Wert nach der MD5-Verschlüsselung ist jedoch fest. Beispielsweise hat das Kennwort 12345678 einen festen MD5-Wert von 25d55ad283aa400af464c76d713c07ad.

Daher kann MD5 mit Brute-Force geknackt werden, um die MD5-Werte zu berechnen, die allen möglichen Zeichenfolgen entsprechen. Wenn es unmöglich ist, alle Zeichenfolgenkombinationen aufzuzählen, können Sie einige gängige Passwörter berechnen, z. B. 111111, 12345678 usw. Auf der Website, die ich in das Dokument eingefügt habe, können MD5-verschlüsselte Zeichenfolgen online entschlüsselt werden.

Daher müssen Sie beim Entwerfen eines Kennwortspeichers Salt hinzufügen. Der Salt-Wert jedes Unternehmens ist unterschiedlich, sodass auch der berechnete Wert unterschiedlich ist. Wenn der Salt-Wert „psalt“ lautet, lautet der Wert des Kennworts 12345678 in der Datenbank:

Passwort = MD5('psalt12345678')

Bei diesem Design der Kennwortspeicherung handelt es sich um einen Verschlüsselungsalgorithmus mit einem festen Salt-Wert, der drei Hauptprobleme aufweist:

Wenn der Salt-Wert von einem (ehemaligen) Mitarbeiter geleakt wird, besteht immer noch die Möglichkeit, dass ein externer Hacker ihn mit großem Gewinn knacken kann;

Bei demselben Kennwort ist der Kennwortspeicherwert derselbe. Sobald das Kennwort eines Benutzers weitergegeben wird, werden auch die Kennwörter anderer Benutzer mit demselben Kennwort weitergegeben.

Der MD5-Verschlüsselungsalgorithmus wird fest verwendet. Sobald der MD5-Algorithmus geknackt ist, sind die Auswirkungen groß.

Daher sollte ein wirklich gutes Design zur Kennwortspeicherung folgendes sein: dynamisches Salt + nicht fester Verschlüsselungsalgorithmus.

Ich empfehle, Passwörter wie folgt zu gestalten. Die Spalte „Passwort“ wird im folgenden Format gespeichert:

$Salz$Verschlüsselungsalgorithmus$Wert

In:

• $salt: steht für dynamisches Salt. Jedes Mal, wenn sich ein Benutzer registriert, generiert das Unternehmen einen anderen Salt-Wert und speichert ihn in der Datenbank. Wenn Sie es anspruchsvoller gestalten möchten, können Sie den dynamischen Salt-Wert und das Benutzerregistrierungsdatum zu einem dynamischeren Salt-Wert kombinieren.
• $cryption_algorithm: gibt den Verschlüsselungsalgorithmus an, z. B. steht v1 für den MD5-Verschlüsselungsalgorithmus, v2 für den AES256-Verschlüsselungsalgorithmus und v3 für den AES512-Verschlüsselungsalgorithmus.
• $value: stellt die verschlüsselte Zeichenfolge dar.

Derzeit sieht die Struktur der Benutzertabelle wie folgt aus:

CREATE TABLE Benutzer (

    id BIGINT NICHT NULL AUTO_INCREMENT,

    Name VARCHAR(255) NOT NULL,

    Geschlecht CHAR(1) NICHT NULL,

    Passwort VARCHAR(1024) NOT NULL,

    regDate DATETIME NICHT NULL,

    PRÜFEN (Geschlecht = ‚M‘ ODER Geschlecht = ‚W‘),

    PRIMÄRSCHLÜSSEL(id)

);



SELECT * FROM Benutzer\G

*************************** 1. Reihe ***************************

      ID: 1

    Name: David

     Geschlecht: M

Passwort: $fgfaef$v1$2198687f6db06c9d1b31a030ba1ef074

 Registrierungsdatum: 07.09.2020 15:30:00

*************************** 2. Reihe ***************************

      ID: 2

    Name: Amy

     Geschlecht: F

Passwort: $zpelf$v2$0x860E4E3B2AA4005D8EE9B7653409C4B133AF77AEF53B815D31426EC6EF78D882

 Registrierungsdatum: 07.09.2020 17:28:00

Im obigen Beispiel lauten die Passwörter der Benutzer David und Amy beide 12345678. Aufgrund der Verwendung von dynamischem Salt und einem dynamischen Verschlüsselungsalgorithmus sind die in den beiden Passwörtern gespeicherten Inhalte jedoch völlig unterschiedlich.

Selbst wenn ein Benutzer mit Hintergedanken den aktuellen Kennwortverschlüsselungsalgorithmus erhält, können die gespeicherten Kennwörter des Benutzers durch die Version des Verschlüsselungsalgorithmus $cryption_algorithm aktualisiert werden, um böswillige Datenangriffe weiter zu verhindern.

Zusammenfassen

Dies ist das Ende dieses Artikels über COLLATION in MySQL, den Sie möglicherweise übersehen haben. Weitere Informationen zu COLLATION in MySQL finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!