Einfügen von NULL in NOT NULL-Spalten mit Standardwert

Question

Für ein wenig Hintergrund verwenden wir bei der Arbeit Zend Framework 2 und Doctrine. Doctrine fügt immer NULL für Werte ein, die wir nicht selbst einfüllen. Normalerweise ist das okay, da das Feld mit dem Standardwert befüllt werden sollte, wenn es einen Standardwert hat.

Für einen unserer Server, auf dem MySQL 5.6.16 läuft, läuft eine Abfrage wie die untenstehende und führt sie aus. Obwohl NULL in ein Feld eingefügt wird, das nicht NULL sein darf, füllt MySQL das Feld beim Einfügen mit seinem Standardwert.

Auf einem anderen unserer Server, auf dem MySQL 5.6.20 läuft, führen wir die untenstehende Abfrage aus und sie schlägt fehl, weil sie sich beschwert, dass 'field_with_default_value' NICHT NULL sein kann.

INSERT INTO table_name(id, field, field_with_default_value) 
VALUES(id_value, field_value, NULL);

Doctrine unterstützt selbst nicht das Durchreichen von "DEFAULT" in die erstellten Abfragen, daher ist dies keine Option. Ich denke, dies muss eine Art MySQL-Serverproblem sein, da es in einer Version funktioniert, aber nicht in einer anderen, aber leider habe ich keine Ahnung, was das sein könnte. Unser SQL-Modus ist auch auf beiden Servern identisch ('NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION').

Ich sollte wahrscheinlich erwähnen, dass, wenn ich den obigen SQL in Workbench ausführe, es immer noch nicht funktioniert. Es handelt sich also nicht wirklich um ein Doctrine-Problem, sondern definitiv um ein MySQL-Problem irgendwelcher Art.

Jede Hilfe dabei wäre sehr geschätzt.

Answer 1

Ich bin nach einem MySQL-Upgrade auf das gleiche Problem gestoßen. Es stellt sich heraus, dass es eine Einstellung gibt, die NULL-Einfügungen gegenüber NOT NULL-Timestamp-Feldern zulässt und den Standardwert abruft.

explicit_defaults_for_timestamp=0

Dies ist dokumentiert unter https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

Answer 2

Basierend auf meiner Recherche würde ich sagen, dass es sowohl an dir liegen könnte als auch an MySQL. Überprüfe deine Tabellendefinitionen mit SHOW CREATE TABLE table_name;. Achte auf alle Felder, die mit NOT NULL definiert sind.

Das MySQL 5.6 Referenzhandbuch: 13.2.5 INSERT-Syntax besagt:

Einsetzen von NULL in eine Spalte, die als NOT NULL deklariert wurde. Für mehrzeilige INSERT-Anweisungen oder INSERT INTO ... SELECT-Anweisungen wird die Spalte auf den impliziten Standardwert für den Spaltendatentyp gesetzt. Dies ist 0 für numerische Typen, der leere String ('') für Zeichentypen und der "Null"-Wert für Datum- und Zeittypen. INSERT INTO ... SELECT-Anweisungen werden genauso behandelt wie mehrzeilige Einfügungen da der Server das Ergebnisset aus dem SELECT nicht untersucht um festzustellen, ob es eine einzelne Zeile zurückgibt. (Für eine einzelne INSERT-Anweisung tritt kein Warnung auf, wenn NULL in eine NOT NULL Spalte eingefügt wird. Stattdessen schlägt die Anweisung mit einem Fehler fehl.)

Dies würde darauf hindeuten, dass es keine Rolle spielt, welchen SQL-Modus du verwendest. Wenn du eine einzige Zeile INSERT (wie in deinem Beispielcode) durchführst und einen NULL-Wert in eine Spalte einfügst, die mit NOT NULL definiert ist, soll es nicht funktionieren.

Ironischerweise besagt das MySQL-Handbuch in demselben Atemzug, dass folgendes zutrifft, und der SQL-Modus spielt in diesem Fall eine Rolle:

Wenn du nicht im strikten SQL-Modus bist, wird eine Spalte ohne expliziten Wert auf ihren Standardwert (explizit oder implizit) gesetzt. Wenn du beispielsweise eine Spaltenliste angibst, die nicht alle Spalten in der Tabelle benennt, werden unbenannte Spalten auf ihre Standardwerte gesetzt. Die Zuweisung von Standardwerten wird im Abschnitt 11.6, "Standardwerte Datentypen", beschrieben. Siehe auch Abschnitt 1.7.3.3, "Einschränkungen für ungültige Daten".

Daher kannst du nicht gewinnen! ;-) Spaß beiseite. Die Sache ist, zu akzeptieren, dass NOT NULL in einer MySQL-Tabellenspalte wirklich bedeutet Ich werde keinen NULL-Wert für ein Feld akzeptieren, wenn ich einen einzelnen INSERT durchführe, unabhängig vom SQL-Modus.

Alles in allem ist das Folgende aus dem Handbuch auch wahr:

Für die Dateneingabe in eine NOT NULL-Spalte ohne explizite DEFAULT-Klausel, wenn eine INSERT- oder REPLACE-Anweisung keinen Wert für die Spalte enthält, oder eine UPDATE-Anweisung die Spalte auf NULL setzt, bearbeitet MySQL die Spalte entsprechend dem SQL-Modus derzeit in Kraft:

Wenn der strenger SQL-Modus aktiviert ist, tritt ein Fehler für transaktionale Tabellen auf und die Anweisung wird zurückgesetzt. Für nicht-transaktionale Tabellen tritt ein Fehler auf, aber wenn dies für die zweite oder weitere Zeile einer mehrzeiligen Anweisung geschieht, wurden die vorangehenden Zeilen bereits eingefügt.

Wenn der strikte Modus nicht aktiviert ist, setzt MySQL die Spalte auf den impliziten Standardwert für den Spaltendatentyp.

Also, sei getrost. Lege deine Standardwerte in der Geschäftslogik (Objekte) fest und lass die Datenschicht davon ausgehen. Standardwerte in der Datenbank scheinen eine gute Idee zu sein, aber würdest du sie vermissen, wenn sie nicht existierten? Wenn ein Baum im Wald fällt...

Answer 3

Laut der Dokumentation funktioniert alles wie erwartet.

Testfall:

mysql> use test;
Das Lesen von Tabelleninformationen zur Vervollständigung von Tabellen- und Spaltennamen
Sie können diese Funktion ausschalten, um einen schnelleren Start mit -A zu erhalten

Datenbank geändert
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.16    |
+-----------+
1 Datensatz in Set (0,00 Sek.)

mysql> SELECT @@GLOBAL.sql_mode 'sql_mode::GLOBAL',
              @@SESSION.sql_mode 'sql_mode::SESSION';
+------------------------+------------------------+
| sql_mode::GLOBAL       | sql_mode::SESSION      |
+------------------------+------------------------+
| NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION |
+------------------------+------------------------+
1 Datensatz in Set (0,00 Sek.)

mysql> SET SESSION sql_mode := 'NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Abfrage OK, 0 Datensätze betroffen (0,00 Sek.)

mysql> SELECT @@GLOBAL.sql_mode 'sql_mode::GLOBAL',
              @@SESSION.sql_mode 'sql_mode::SESSION';
+------------------------+-----------------------------------------------------------------------------------------------------------------+
| sql_mode::GLOBAL       | sql_mode::SESSION                                                                                               |
+------------------------+-----------------------------------------------------------------------------------------------------------------+
| NO_ENGINE_SUBSTITUTION | NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------+-----------------------------------------------------------------------------------------------------------------+
1 Datensatz in Set (0,00 Sek.)

mysql> SHOW CREATE TABLE `table_name`;
+------------+----------------------------------------------------------------------------+
| Table      | Create Table                                                               |
+------------+----------------------------------------------------------------------------+
| table_name | CREATE TABLE `table_name` (                                                |
|            |        `id` INT(11) UNSIGNED NOT NULL,                                     |
|            |        `field` VARCHAR(20) DEFAULT NULL,                                   |
|            |        `field_with_default_value` VARCHAR(20) NOT NULL DEFAULT 'myDefault' |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=latin1                                     |
+------------+----------------------------------------------------------------------------+
1 Datensatz in Set (0,00 Sek.)

mysql> INSERT INTO `table_name`(`id`, `field`, `field_with_default_value`)
       VALUES
       (1, 'Value', NULL);
FEHLER 1048 (23000): Column 'field_with_default_value' cannot be null

Ist es möglich, den relevanten Teil der Struktur Ihrer Tabelle zu posten, um zu sehen, wie wir helfen können?

UPDATE

MySQL 5.7 bietet mit Triggern eine mögliche Lösung für das Problem:

Änderungen in MySQL 5.7.1 (2013-04-23, Meilenstein 11)

...

• Wenn eine Spalte als NOT NULL deklariert ist, ist es nicht erlaubt, NULL in die Spalte einzufügen oder sie auf NULL zu aktualisieren. Diese Einschränkung wurde jedoch durchgesetzt, auch wenn ein BEFORE INSERT (oder BEFORE UPDATE Trigger) die Spalte auf einen nicht-NULL-Wert setzte. Jetzt wird die Einschränkung am Ende der Anweisung überprüft, gemäß dem SQL-Standard. (Fehler #6295, Bug#11744964).

...

Mögliche Lösung:

mysql> use test;
Das Lesen von Tabelleninformationen zur Vervollständigung von Tabellen- und Spaltennamen
Sie können diese Funktion ausschalten, um einen schnelleren Start mit -A zu erhalten

Datenbank geändert
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.4-m14 |
+-----------+
1 Datensatz in Set (0,00 sek.)

mysql> DELIMITER $$

mysql> CREATE TRIGGER `trg_bi_set_default_value` BEFORE INSERT ON `table_name`
       FOR EACH ROW
       BEGIN
          IF (NEW.`field_with_default_value` IS NULL) THEN
             SET NEW.`field_with_default_value` := 
                (SELECT `COLUMN_DEFAULT`
                 FROM `information_schema`.`COLUMNS`
                 WHERE `TABLE_SCHEMA` = DATABASE() AND 
                       `TABLE_NAME` = 'table_name' AND
                       `COLUMN_NAME` = 'field_with_default_value');
          END IF;
       END$$

mysql> DELIMITER ;

mysql> INSERT INTO `table_name`(`id`, `field`, `field_with_default_value`)
       VALUES
       (1, 'Value', NULL);
Abfrage OK, 1 Datensatz betroffen (0,00 sek.)

mysql> SELECT `id`, `field`, `field_with_default_value` FROM `table_name`;
+----+-------+--------------------------+
| id | field | field_with_default_value |
+----+-------+--------------------------+
|  1 | Value | myDefault                |
+----+-------+--------------------------+
1 Datensatz in Set (0,00 Sek.)

Answer 4

MySQL funktioniert tatsächlich wie beabsichtigt, und dieses Verhalten scheint bestehen zu bleiben. MariaDB funktioniert jetzt auch auf die gleiche Weise.

Das Entfernen von "strict mode" (STRICT_TRANS_TABLES & STRICT_ALL_TABLES) sollte zu dem vorherigen Verhalten zurückkehren, aber persönlich hatte ich damit kein Glück (vielleicht mache ich etwas falsch, aber weder mein @@GLOBAL.sql_mode noch mein @@SESSION.sql_mode enthalten den strict mode).

Ich denke, die beste Lösung für dieses Problem besteht darin, sich auf Standardwerte auf PHP-Ebene zu verlassen, anstatt darauf zu vertrauen, dass die Datenbank sie bereitstellt. Es gibt bereits eine Antwort, die es ziemlich gut erklärt. Die Kommentare sind auch hilfreich.

Auf diese Weise erhalten Sie auch den zusätzlichen Vorteil, dass Ihre Modelle/Entitäten den Standardwert beim Instanziieren anstelle von beim Einfügen in die Datenbank haben. Außerdem können Sie, wenn Sie diese Werte nach dem Einfügen dem Benutzer anzeigen möchten, dies tun, ohne nach Ihrem INSERT eine zusätzliche SELECT-Abfrage durchführen zu müssen.

Eine weitere Alternative, um die Standardwerte an die Oberfläche zu bringen, wäre die Verwendung einer RETURNING-Klausel, wie sie in PostgreSQL verfügbar ist, aber nicht in MySQL (noch). Es könnte in Zukunft hinzugefügt werden, aber bisher hat MariaDB es nur für DELETE-Anweisungen. Ich glaube jedoch, dass das Vorhandensein der Standardwerte auf PHP-Ebene immer noch überlegen ist; selbst wenn Sie den Datensatz nie einfügen, enthält er immer noch die Standardwerte. Ich bin seit der Umsetzung dieser Methode nie zurückgegangen und habe einen Datenbankstandardwert verwendet.

Answer 5

Wenn Sie die Spalte (Name und Wert) aus der Anweisung entfernen, wird der Standardwert verwendet.

Einige verwandte Ratschläge:

• Keine "nicht-leere" Standardeinstellungen in Ihren Tabellen haben und keine nicht-null Standardeinstellungen für nullable Spalten haben. Lassen Sie alle Werte von der Anwendung setzen.
• Platzieren Sie keine Geschäftslogik auf der Datenbankseite.

Definieren Sie nur einen Standard, wenn wirklich nötig, und nur für nicht-nullable Spalten. Und entfernen Sie den Standard, wenn er nicht mehr benötigt wird. (Sie sind praktisch bei Änderungsläufen von alter table, um den Wert einer neuen Spalte zu setzen, führen dann aber sofort einen neuen (günstigen!) alter aus, um den Standard zu entfernen)

Das oben genannte "leer" bezieht sich auf den Typ: - 0 für numerische Spalten, - '' für varchar/varbinary Spalten, - '1970-01-01 12:34:56' für Zeitstempel, - usw.

Das spart der Anwendung viele Hin- und Rückfahrten zur Datenbank. Wenn eine erstellte Zeile vollständig vorhersehbar ist, muss die Anwendung sie nach dem Erstellen nicht lesen, um herauszufinden, was daraus geworden ist. (das setzt voraus: keine Trigger, kein Kaskadieren)

Bei MySQL machen wir nur wenige spezifische Ausnahmen von diesen strengen Regeln:

• Spalten mit dem Namen mysql_row_foo werden nur von der Datenbank gesetzt. Beispiele:

    mysql\_row\_created\_at  timestamp(6)  not null  default '1970-01-01 12:34:56.000000',
    mysql\_row\_updated\_at  timestamp(6)  null      default null  on update current\_timestamp,

• Eindeutige Indizes auf nicht-null Spalten sind willkommen, um doppelte Daten zu verhindern. Zum Beispiel auf lookup.brand.name in einer Tabelle lookup.brand, die so aussieht (id++, name).

Die mysql_row_foo Spalten sind wie Spalteneigenschaften. Sie werden von Datensynchronisierungstools verwendet, zum Beispiel. Allgemeine Anwendungen lesen sie nicht, und sie speichern ihre Anwendungseigenen Zeitstempel als Epochenwerte. Beispiele:

 valid\_until\_epoch   int unsigned  not null  default 0,
 last\_seen\_epoch\_ms  bigint        not null  default 0,