Implementierung von Check Constraints in MySQL 8.0

Hallo zusammen, ich bin Tony, ein Lehrer, der nur über Techniken spricht und keine Haare schneidet. Dieses Mal stellen wir eine neue Funktion vor, die in MySQL 8.0 hinzugefügt wurde: Check Constraint (CHECK).

Die Prüfbedingung in SQL ist eine Art Integritätsbedingung, die verwendet werden kann, um ein Feld oder einige Felder in einer Tabelle so einzuschränken, dass sie eine bestimmte Bedingung erfüllen. Beispielsweise muss der Benutzername groß geschrieben werden und der Kontostand darf nicht kleiner als null sein.

Unsere gängigen Datenbanken wie Oracle, SQL Server, PostgreSQL und SQLite implementieren alle Check Constraints. MySQL hat diese Funktion jedoch erst ab der neuesten Version MySQL 8.0.16 implementiert.

Vor MySQL 8.0.15

Obwohl in MySQL 8.0.15 und früheren Versionen die Anweisung CREATE TABLE eine Check-Constraint-Syntax der Form CHECK (Ausdruck) zulässt, wird die Klausel nach der Analyse tatsächlich ignoriert. Zum Beispiel

mysql> Version auswählen();
+-------------+
| version() |
+-------------+
| 8.0.15 |
+-------------+
1 Zeile im Satz (0,00 Sek.)

mysql> TABELLE ERSTELLEN t1
  -> (
  -> c1 INT-Prüfung (c1 > 10),
  -> c2 INT,
  -> c3 INT-Prüfung (c3 < 100),
  -> CONSTRAINT c2_positive PRÜFUNG (c2 > 0),
  -> PRÜFEN (c1 > c3)
  -> );
Abfrage OK, 0 Zeilen betroffen (0,33 Sek.)

mysql> anzeigen, Tabelle erstellen t1\G
*************************** 1. Reihe ***************************
    Tabelle: t1
Tabelle erstellen: CREATE TABLE `t1` (
 `c1` int(11) DEFAULT NULL,
 `c2` int(11) DEFAULT NULL,
 `c3` int(11) STANDARD NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 Zeile im Satz (0,00 Sek.)

Obwohl wir während der Definition verschiedene CHECK-Optionen angegeben haben, enthält die endgültige Tabellenstruktur keine Prüfbeschränkungen. Dies bedeutet auch, dass wir illegale Daten einfügen können:

mysql> einfügen in t1(c1, c2, c3) Werte(1, -1, 100);
Abfrage OK, 1 Zeile betroffen (0,06 Sek.)

Wenn wir eine ähnliche Prüfbedingung vor MySQL 8.0.15 implementieren möchten, können wir Trigger verwenden oder eine Ansicht mit der Option WITH CHECK OPTION erstellen und dann Daten über die Ansicht einfügen oder ändern.

MySQL 8.0.16 und höher

MySQL 8.0.16 wurde am 25. April 2019 veröffentlicht und brachte endlich die lang erwartete CHECK-Constraint-Funktion, die für alle Speicher-Engines gültig ist. Die Anweisung CREATE TABLE lässt die folgenden Formen der CHECK-Einschränkungssyntax zu, mit denen sowohl Einschränkungen auf Spaltenebene als auch auf Tabellenebene angegeben werden können:

[CONSTRAINT [symbol]] CHECK (expr) [[NICHT] ERZWUNGEN]

Der optionale Symbolparameter wird verwendet, um der Einschränkung einen Namen zuzuweisen. Wenn diese Option weggelassen wird, generiert MySQL einen Namen (table_name_chk_n), der mit dem Tabellennamen plus _chk_ und einer numerischen Zahl (1, 2, 3, …) beginnt. Einschränkungsnamen haben eine maximale Länge von 64 Zeichen und unterscheiden zwischen Groß- und Kleinschreibung.

expr ist ein Boolescher Ausdruck, der die Bedingung der Einschränkung angibt; jede Datenzeile in der Tabelle muss expr erfüllen, um als TRUE oder UNKNOWN (NULL) ausgewertet zu werden. Wenn der Ausdruck FALSE ergibt, wird die Einschränkung verletzt.

Die optionale ENFORCED-Klausel gibt an, ob die Einschränkung erzwungen werden soll:

• Wenn ENFORCED weggelassen oder angegeben wird, wird die Einschränkung erstellt und erzwungen.
• Wenn NOT ENFORCED angegeben ist, wird die Einschränkung erstellt, aber nicht erzwungen. Dies bedeutet auch, dass die Einschränkungen nicht greifen.

CHECK-Einschränkungen können auf Spaltenebene oder auf Tabellenebene angegeben werden.

Prüfbeschränkungen auf Spaltenebene

Einschränkungen auf Spaltenebene können nur nach einer Felddefinition erscheinen und können nur für dieses Feld eingeschränkt werden. Zum Beispiel:

mysql> Version auswählen();
+-------------+
| version() |
+-------------+
| 8.0.16 |
+-------------+
1 Zeile im Satz (0,00 Sek.)

mysql> TABELLE ERSTELLEN t1
  -> (
  -> c1 INT-Prüfung (c1 > 10),
  -> c2 INT CONSTRAINT c2_positive PRÜFUNG (c2 > 0),
  -> c3 INT-Prüfung (c3 < 100)
  -> );
Abfrage OK, 0 Zeilen betroffen (0,04 Sek.)

mysql> anzeigen, Tabelle erstellen t1\G
*************************** 1. Reihe ***************************
    Tabelle: t1
Tabelle erstellen: CREATE TABLE `t1` (
 `c1` int DEFAULT NULL,
 `c2` int DEFAULT NULL,
 `c3` int DEFAULT NULL,
 Einschränkung `c2_positive` PRÜFUNG ((`c2` > 0)),
 Einschränkung `t1_chk_1` PRÜFUNG ((`c1` > 10)),
 Einschränkung `t1_chk_2` PRÜFUNG ((`c3` < 100))
) ENGINE=InnoDB STANDARD-CHARSET=utf8
1 Zeile im Satz (0,00 Sek.)

Die Prüfbeschränkungen für die Felder c1 und c3 verwenden systemgenerierte Namen; die Prüfbeschränkungen für c2 verwenden einen benutzerdefinierten Namen.

Alle Constraints im SQL-Standard (Primärschlüssel, Unique Constraint, Fremdschlüssel, Check Constraint usw.) gehören zum selben Namespace, d. h. sie können nicht denselben Namen haben. In MySQL gehört jedoch jeder Einschränkungstyp in der Datenbank zu seinem eigenen Namespace. Daher können ein Primärschlüssel und eine Prüfeinschränkung denselben Namen haben, aber zwei Prüfeinschränkungen können nicht denselben Namen haben.

Wir fügen Testdaten ein:

mysql> einfügen in t1(c1, c2, c3) Werte(1, -1, 100);
FEHLER 3819 (HY000): Die Prüfbedingung „c2_positive“ ist verletzt.

Alle drei Felder der eingefügten Daten verletzen die Einschränkungen. Das Ergebnis zeigt, dass c2_positive verletzt ist. Da es nach Namen an erster Stelle steht, ist ersichtlich, dass MySQL die Einschränkungen der Reihe nach nach Namen überprüft.

Lassen Sie uns weitere Testdaten einfügen:

mysql> einfügen in t1(c1, c2, c3) Werte(null, null, null);
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

Die Daten wurden erfolgreich eingefügt, sodass der NULL-Wert die Prüfbedingung nicht verletzt.

Prüfbeschränkungen auf Tabellenebene

Einschränkungen auf Tabellenebene sind unabhängig von Felddefinitionen und können auf mehrere Felder angewendet werden, sogar vor den Felddefinitionen. Zum Beispiel:

mysql> Tabelle t1 löschen;
Abfrage OK, 0 Zeilen betroffen (0,04 Sek.)

mysql> TABELLE ERSTELLEN t1
  -> (
  -> PRÜFEN (c1 <> c2),
  -> c1 INT,
  -> c2 INT,
  -> c3 INT,
  -> CONSTRAINT c1_nonzero CHECK (c1 <> 0),
  -> PRÜFEN (c1 > c3)
  -> );
Abfrage OK, 0 Zeilen betroffen (0,04 Sek.)

mysql> anzeigen, Tabelle erstellen t1\G
*************************** 1. Reihe ***************************
    Tabelle: t1
Tabelle erstellen: CREATE TABLE `t1` (
 `c1` int DEFAULT NULL,
 `c2` int DEFAULT NULL,
 `c3` int DEFAULT NULL,
 Einschränkung `c1_nonzero` Prüfung ((`c1` <> 0)),
 EINSCHRÄNKUNG `t1_chk_1` PRÜFUNG ((`c1` <> `c2`)),
 EINSCHRÄNKUNG `t1_chk_2` PRÜFUNG ((`c1` > `c3`))
) ENGINE=InnoDB STANDARD-CHARSET=utf8
1 Zeile im Satz (0,00 Sek.)

Die erste Einschränkung, t1_chk_1, erscheint vor den Felddefinitionen, kann aber immer noch auf c1 und c2 verweisen; die zweite Einschränkung, c1_nonzero, verwendet einen benutzerdefinierten Namen; und die dritte Einschränkung, t1_chk_2, erscheint nach allen Felddefinitionen.

Wir fügen auch einige Testdaten ein:

mysql> einfügen in t1(c1, c2, c3) Werte(1, 2, 3);
FEHLER 3819 (HY000): Die Prüfbedingung „t1_chk_2“ wurde verletzt.

mysql> einfügen in t1(c1, c2, c3) Werte(null, 2, 3);
Abfrage OK, 1 Zeile betroffen (0,01 Sek.)

Im ersten Datensatz ist c1 kleiner als c3, was die Prüfbedingung t1_chk_2 verletzt. Im zweiten Datensatz ist c1 NULL und das Ergebnis der Prüfbedingung t1_chk_2 ist UNBEKANNT, was die Bedingung nicht verletzt.

Obligatorische Optionen

Einschränkungen, die im Standardmodus oder mit der Option ENFORCED erstellt wurden, müssen geprüft werden. Wir können sie auch in NOT ENFORCED ändern, um die Prüfung zu ignorieren:

ALTER TABLE Tabellenname
ALTER {CHECK | CONSTRAINT} Symbol [NICHT] ERZWUNGEN

Der geänderte Check Constraint bleibt bestehen, es wird jedoch keine Prüfung durchgeführt. Zum Beispiel:

mysql> Tabelle t1 ändern 
  -> Änderungsprüfung t1_chk_1 nicht erzwungen;
Abfrage OK, 0 Zeilen betroffen (0,02 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

mysql> anzeigen, Tabelle erstellen t1\G
*************************** 1. Reihe ***************************
    Tabelle: t1
Tabelle erstellen: CREATE TABLE `t1` (
 `c1` int DEFAULT NULL,
 `c2` int DEFAULT NULL,
 `c3` int DEFAULT NULL,
 Einschränkung `c1_nonzero` Prüfung ((`c1` <> 0)),
 CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)) /*!80016 NICHT ERZWUNGEN */,
 EINSCHRÄNKUNG `t1_chk_2` PRÜFUNG ((`c1` > `c3`))
) ENGINE=InnoDB STANDARD-CHARSET=utf8
1 Zeile im Satz (0,00 Sek.)

Aus der neuesten Definition können wir ersehen, dass sich t1_chk_1 im Status „NICHT ERZWUNGEN“ befindet. Wir fügen Daten ein, die diese Einschränkung verletzen:

mysql> einfügen in t1(c1, c2, c3) Werte(1, 1, 0);
Abfrage OK, 1 Zeile betroffen (0,01 Sek.)

c1 und c2 dieses Datensatzes sind gleich, aber die Einfügung ist erfolgreich.

Wenn wir einige historische Daten niedrigerer Versionen migrieren müssen, verstoßen diese möglicherweise gegen die neue Prüfbedingung. In diesem Fall können wir die Bedingung zuerst deaktivieren und die Durchsetzungsoption dann wieder aktivieren, nachdem die Daten migriert und verarbeitet wurden.

Überprüfen der Einschränkungsgrenzen

Der CHECK-Bedingungsausdruck in MySQL muss die folgenden Regeln erfüllen, sonst kann die Check-Einschränkung nicht erstellt werden:

• Nicht berechnete und berechnete Spalten sind zulässig, aber AUTO_INCREMENT-Felder oder Felder aus anderen Tabellen sind nicht zulässig.
• Erlaubt sind Literale, deterministische integrierte Funktionen (die das gleiche Ergebnis erzeugen, auch wenn sie von verschiedenen Benutzern mit der gleichen Eingabe mehrfach aufgerufen werden) und Operatoren. Zu den nicht-deterministischen Funktionen zählen: CONNECTION_ID(), CURRENT_USER(), NOW() usw. Sie können nicht zum Überprüfen von Einschränkungen verwendet werden.
• Gespeicherte Funktionen oder benutzerdefinierte Funktionen sind nicht zulässig.
• Gespeicherte Prozeduren und Funktionsparameter sind nicht zulässig.
• Variablen sind nicht zulässig, einschließlich Systemvariablen, benutzerdefinierter Variablen und lokaler Variablen gespeicherter Prozeduren.
• Unterabfragen sind nicht zulässig.

Darüber hinaus werden referenzielle Operationen (ON UPDATE, ON DELETE), die Fremdschlüsseleinschränkungen für CHECK-Einschränkungsfelder definieren, deaktiviert. Ebenso dürfen CHECK-Einschränkungen nicht für Felder erstellt werden, die referenzielle Operationen mit Fremdschlüsseleinschränkungen aufweisen.

Bei INSERT-, UPDATE-, REPLACE-, LOAD DATA- und LOAD XML-Anweisungen werden Fehler zurückgegeben, wenn Prüfbeschränkungen verletzt werden. An diesem Punkt hängt die Verarbeitung der geänderten Daten davon ab, ob die Speicher-Engine Transaktionen unterstützt und ob der strikte SQL-Modus verwendet wird.

Bei den Anweisungen INSERT IGNORE, UPDATE IGNORE, REPLACE, LOAD DATA ... IGNORE und LOAD XML ... IGNORE werden bei Verstößen gegen Prüfbeschränkungen Warnungen zurückgegeben und die betreffenden Zeilen übersprungen.

Wenn der Ergebnistyp des Einschränkungsausdrucks vom Datentyp der Spalte abweicht, führt MySQL eine implizite Typkonvertierung durch. Wenn die Typkonvertierung fehlschlägt oder an Genauigkeit verliert, wird ein Fehler zurückgegeben.

Zusammenfassen

Die neuen Prüfbeschränkungen, die in MySQL 8.0.16 hinzugefügt wurden, verbessern die Fähigkeit von MySQL, Geschäftsintegritätsbeschränkungen zu implementieren und MySQL konformer mit SQL-Standards zu machen.

Dies ist das Ende dieses Artikels über die Implementierung von Check Constraints, einer neuen Funktion von MySQL 8.0. Weitere Informationen zu MySQL 8.0 Check Constraints 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!