Skip navigation links
1.9.6.3 ENUM- und SET-Constraints »
Section Navigation      [Toggle]

1.9.6.2. Constraints auf ungültigen Daten

Vor MySQL 5.0.2 verzeiht MySQL unzulässige oder unzutreffende Dateneingaben und setzt diese nach der Eingabe auf zulässige Werte. Bei MySQL 5.0.2 und höher bleibt dies zwar auch das Standardverhalten, aber Sie können den SQL-Modus des Servers so ändern, dass ein traditionellerer Umgang mit solchen Daten gewählt wird: Der Server kann sie dann auch abweisen und die Anweisung abbrechen, in der sie auftreten. Abschnitt 5.2.5, „Der SQL-Modus des Servers“.

Dieser Abschnitt beschreibt das (nachsichtige) Standardverhalten von MySQL und den neueren strikten SQL-Modus sowie die Unterschiede zwischen diesen Modi.

Wenn Sie den strikten Modus nicht verwenden, dann wird, wann immer Sie einen „falschen“ Wert in eine Spalte einsetzen (z. B. eine NULL in eine NOT NULL-Spalte oder einen zu großen numerischen Wert in eine numerische Spalte), MySQL die Spalte auf den „bestmöglichen“ Wert setzen, statt einen Fehler zu erzeugen. Die folgenden Regeln beschreiben genauer, wie dies funktioniert:

  • Wenn Sie versuchen, einen Wert außerhalb des zulässigen Bereichs in einer numerischen Spalte zu speichern, dann speichert MySQL Server stattdessen null, den kleinstmöglichen oder den größtmöglichen Wert – abhängig davon, welcher gültige Wert am nächsten liegt.

  • Bei Strings speichert MySQL entweder den Leer-String oder den Teil des Strings, der sich in der Spalte speichern lässt.

  • Wenn Sie in einer numerischen Spalte einen String speichern wollen, der nicht mit einer Zahl beginnt, dann speichert MySQL Server 0.

  • Mit ungültigen Werten für ENUM- und SET-Spalten wird wie in Abschnitt 1.9.6.3, „ENUM- und SET-Constraints“, beschrieben verfahren.

  • MySQL gestattet die Speicherung bestimmter unzulässiger Werte in DATE- und DATETIME-Spalten (z. B. '2000-02-31' oder '2000-02-00'). Der Grundgedanke dahinter ist der, dass es nicht Aufgabe des SQL-Servers sein kann, Datumsangaben auszuwerten. Wenn MySQL einen Datumswert speichern und den exakt gleichen Wert wieder abrufen kann, dann speichert MySQL ihn auch wie eingegeben. Ist das Datum jedoch völlig falsch (d. h., es kann nicht vom Server gespeichert werden), dann wird stattdessen der spezielle „Nulldatumswert“ '0000-00-00' in der Spalte abgelegt.

  • Wenn Sie NULL in einer Spalte speichern wollen, die keine NULL-Werte akzeptiert, dann erscheint bei INSERT-Anweisungen für einzelne Datensätze ein Fehler. Bei INSERT-Anweisungen für mehrere Datensätze oder INSERT INTO ... SELECT-Anweisungen hingegen speichert MySQL Server den impliziten Vorgabewert für den Datentyp der Spalte. In der Regel ist dies 0 bei numerischen Typen, der Leer-String ('') bei String-Typen und der „Nullwert“ für Datums- und Uhrzeittypen. Implizite Vorgabewerte werden in Abschnitt 11.1.4, „Vorgabewerte von Datentypen“, behandelt.

  • Wenn eine INSERT-Anweisung keinen Wert für eine Spalte angibt, fügt MySQL den Vorgabewert ein, sofern die Spaltendefinition eine explizite DEFAULT-Klausel enthält. Ist eine solche DEFAULT-Klausel nicht in der Definition enthalten, dann fügt MySQL den impliziten Vorgabewert für den Datentyp der Spalte ein.

Der Grund für die Verwendung obiger Regeln im nicht strikten Modus ist, dass wir diese Bedingungen erst nach Beginn der Ausführung der betreffenden Anweisung überprüfen können. Tritt nach der Aktualisierung einiger Datensätze ein Problem auf, dann können wir nicht einfach einen Rollback durchführen, da die Speicher-Engine Rollbacks unter Umständen nicht unterstützt. Die Option, die Anweisung einfach zu beenden, wäre nachteilig; in diesem Fall wäre die Aktualisierung nämlich nur „zur Hälfte“ erfolgt, was das wohl schlimmste vorstellbare Szenario wäre. In einem solchen Fall ist es besser, das „Bestmögliche“ zu tun und dann fortzufahren, so als ob nichts gewesen wäre.

In MySQL 5.0.2 und höher können Sie eine strengere Behandlung der Eingabewerte mithilfe der SQL-Modi STRICT_TRANS_TABLES oder STRICT_ALL_TABLES auswählen: 

SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';

STRICT_TRANS_TABLES aktiviert den strikten Modus für transaktionale Speicher-Engines und bis zu einem gewissen Grad auch für nichttransaktionale Engines. Das funktioniert wie folgt:

  • Bei transaktionalen Speicher-Engines wird bei Auftreten unzulässiger Werte in einer Anweisung die Ausführung dieser Anweisung abgebrochen und ein Rollback durchgeführt.

  • Bei nichttransaktionalen Speicher-Engines wird die Anweisung abgebrochen, wenn der Fehler beim ersten einzufügenden bzw. zu aktualisierenden Datensatz auftritt. (Tritt der Fehler beim ersten Datensatz auf, dann kann die Anweisung wie bei transaktionalen Tabellen abgebrochen werden, ohne dass Änderungen in der Tabelle erfolgt wären.) Fehler in nachfolgenden Datensätzen brechen die Anweisung nicht ab, denn in der Tabelle wurden bereits Änderungen vorgenommen. Stattdessen werden unzulässige Datensätze korrigiert und Warnungen (anstelle von Fehlern) erzeugt. Mit anderen Worten: Bei STRICT_TRANS_TABLES hat ein falscher Wert zur Folge, dass MySQL einen Rollback aller bereits durchgeführten Aktualisierungen vornimmt, sofern dies ohne Änderungen in der Tabelle möglich ist. Wurde die Tabelle jedoch bereits geändert, dann führen weitere Fehler zu Korrekturen und Warnungen.

Für eine noch striktere Prüfung aktivieren Sie STRICT_ALL_TABLES. Dies entspricht weitgehend STRICT_TRANS_TABLES, nur führen Fehler bei nichttransaktionalen Speicher-Engines hier dazu, dass die Anweisung auch bei unzulässigen Daten im zweiten oder allen folgenden Datensätzen abgebrochen wird. Das bedeutet, dass, wenn bei einer nichttransaktionalen Tabelle im Verlauf einer Einfüge- oder Aktualisierungsoperation für mehrere Datensätze ein Fehler auftritt, nur eine Teilaktualisierung erfolgt. Datensätze vor Auftreten des Fehlers werden eingefügt bzw. aktualisiert, Datensätze nach dem Fehler jedoch nicht. Um dieses Verhalten bei nicht transaktionalen Tabellen zu vermeiden, verwenden Sie entweder Anweisungen für einzelne Datensätze oder aber STRICT_TRANS_TABLES, sofern Konvertierungswarnungen (statt Fehlern) akzeptabel sind. Um Probleme bereits im Vorfeld zu vermeiden, sollten Sie zur Überprüfung des Spalteninhalts nicht MySQL verwenden. Es ist sicherer (und oft auch schneller), wenn die Anwendung sicherstellt, dass sie nur zulässige Werte an die Datenbank übermittelt.

Bei Auswahl eines strikten Modus können Sie die Behandlung von Fehlern als Warnungen vorsehen, indem Sie INSERT IGNORE bzw. UPDATE IGNORE statt INSERT oder UPDATE ohne IGNORE verwenden.

Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.