MySQL Falscher Datumswert: '0000-00-00 00:00:00'

Ich habe kürzlich ein altes Projekt übernommen, das vor 10 Jahren erstellt wurde. Es verwendet MySQL 5.1.

Unter anderem muss ich den Standardzeichensatz von latin1 auf utf8 ändern.

Ich habe zum Beispiel Tabellen wie die folgende:

  CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `first_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `last_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `username` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `email` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `pass` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `active` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'Y',
    `created` datetime NOT NULL,
    `last_login` datetime DEFAULT NULL,
    `author` varchar(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT 'N',
    `locked_at` datetime DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    `ripple_token` varchar(36) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `ripple_token_expires` datetime DEFAULT '2014-10-31 08:03:55',
    `authentication_token` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `index_users_on_reset_password_token` (`reset_password_token`),
    UNIQUE KEY `index_users_on_confirmation_token` (`confirmation_token`),
    UNIQUE KEY `index_users_on_unlock_token` (`unlock_token`),
    KEY `users_active` (`active`),
    KEY `users_username` (`username`),
    KEY `index_users_on_email` (`email`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1677 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

Ich habe meinen eigenen Mac eingerichtet, um daran zu arbeiten. Ohne groß darüber nachzudenken, habe ich "brew install mysql" ausgeführt, wodurch MySQL 5.7 installiert wurde. Ich habe also einige Versionskonflikte.

Ich lud eine Kopie dieser Datenbank herunter und importierte sie.

Wenn ich versuche, eine Abfrage wie diese auszuführen:

  ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci    NOT NULL

erhalte ich diesen Fehler:

  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

Ich dachte, ich könnte das mit beheben:

  ALTER TABLE users MODIFY created datetime  NULL DEFAULT '1970-01-01 00:00:00';
  Query OK, 0 rows affected (0.06 sec)
  Records: 0  Duplicates: 0  Warnings: 0

aber ich bekomme:

  ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci    NOT NULL ;
  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

Muss ich jeden Wert aktualisieren?

Programmierung
mysql
22. Februar 2016 в 10:23
6 Aufrufe

Ich war nicht in der Lage, dies zu tun:

UPDATE users SET created = NULL WHERE created = '0000-00-00 00:00:00'

(unter MySQL 5.7.13).

Ich bekam immer wieder den Fehler "Falscher Datumswert: '0000-00-00 00:00:00'`.

Seltsamerweise funktionierte dies: SELECT * FROM users WHERE created = '0000-00-00 00:00:00'. Ich habe keine Ahnung, warum ersteres fehlschlägt und letzteres funktioniert... vielleicht ein MySQL-Fehler?

Auf jeden Fall hat diese UPDATE-Abfrage funktioniert:

UPDATE users SET created = NULL WHERE CAST(created AS CHAR(20)) = '0000-00-00 00:00:00'
Kommentare (8)

Ändern des Standardwertes für eine Spalte mit einer ALTER TABLE-Anweisung, z.B.

 ALTER TABLE users MODIFY created datetime  NULL DEFAULT '1970-01-02'

... ändert keine Werte, die bereits gespeichert sind. Der "Standardwert" gilt für Zeilen, die eingefügt werden und für die kein Wert für die Spalte geliefert wird.

Was den Grund für den Fehler angeht, so ist es wahrscheinlich, dass die sql_mode-Einstellung für Ihre Sitzung NO_ZERO_DATE enthält.

Referenz: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

Als Sie den "Import" durchführten, wurden die SQL-Anweisungen, die das INSERT in diese Tabelle durchführten, in einer Sitzung ausgeführt, die Nulldaten zuließ.

Um die sql_mode-Einstellung zu sehen:

SHOW VARIABLES LIKE 'sql_mode' ;

-oder-

SELECT @@sql_mode ;

Wie kann man das aktuelle Problem beheben, so dass der Fehler nicht mehr auftritt, wenn man die Anweisung "ALTER TABLE" ausführt?

Es gibt mehrere Möglichkeiten:

  1. Ändern Sie den sql_mode so, dass er Null-Daten zulässt, indem Sie NO_ZERO_DATE und NO_ZERO_IN_DATE entfernen. Die Änderung kann in der Datei my.cnf vorgenommen werden, so dass nach einem Neustart des MySQL-Servers die Variable sql_mode mit der Einstellung in my.cnf initialisiert wird.

Für eine temporäre Änderung können wir die Einstellung mit einer einzigen Sitzung ändern, ohne dass eine globale Änderung erforderlich ist.

-- save current setting of sql_mode
SET @old_sql_mode := @@sql_mode ;

-- derive a new value by removing NO_ZERO_DATE and NO_ZERO_IN_DATE
SET @new_sql_mode := @old_sql_mode ;
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_DATE,'  ,','));
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_IN_DATE,',','));
SET @@sql_mode := @new_sql_mode ;

-- perform the operation that errors due to "zero dates"

-- when we are done with required operations, we can revert back
-- to the original sql_mode setting, from the value we saved
SET @@sql_mode := @old_sql_mode ;

  1. Ändern Sie die Spalte created, um NULL-Werte zuzulassen, und aktualisieren Sie die vorhandenen Zeilen, um die Null-Daten in Null-Werte zu ändern

  2. aktualisieren Sie die vorhandenen Zeilen, um die Null-Daten in ein gültiges Datum zu ändern

Wir müssen keine einzelnen Anweisungen ausführen, um jede Zeile zu aktualisieren. Wir können alle Zeilen auf einen Schlag aktualisieren (vorausgesetzt, es handelt sich um eine Tabelle von angemessener Größe). Bei einer größeren Tabelle können wir die Operation in angemessen großen Stücken durchführen, um ein riesiges Rollback/Undo zu vermeiden).

Der in der Frage angegebene Wert "AUTO_INCREMENT" für die Tabellendefinition gibt uns die Sicherheit, dass die Anzahl der Zeilen nicht zu hoch ist.

Wenn wir die Spalte created bereits geändert haben, um NULL-Werte zuzulassen, können wir etwas wie folgt tun:

UPDATE  `users` SET `created` = NULL WHERE `created` = '0000-00-00 00:00:00'

Oder wir können sie auf ein gültiges Datum setzen, z.B. 2. Januar 1970

UPDATE  `users` SET `created` = '1970-01-02' WHERE `created` = '0000-00-00 00:00:00'

(Beachten Sie, dass ein datetime-Wert von Mitternacht des 1. Januar 1970 ('1970-01-01 00:00:00') *ein "Null-Datum" ist. Dies wird als '0000-00-00 00:00:00' ausgewertet.

Yoan Tournade
Bearbeitete Antwort 13. Juli 2017 в 2:01
Kommentare (5)
Lösung

Wenn die Tabelle leer oder nicht sehr groß ist, schlage ich vor, die Erstellungsanweisungen als .sql-Datei zu exportieren und sie nach Belieben umzuschreiben. Machen Sie dasselbe, wenn Sie bereits Daten haben, d.h. exportieren Sie die Einfügeanweisungen (ich empfehle, dies in einer separaten Datei wie die Erstellungsanweisungen zu tun). Zum Schluss lassen Sie die Tabelle fallen und führen zuerst die create-Anweisungen und dann die inserts aus.

Sie können dafür entweder den Befehl mysqldump verwenden, der in Ihrer MySQL-Installation enthalten ist, oder Sie können auch MySQL Workbench installieren, ein kostenloses grafisches Tool, das auch diese Option auf sehr anpassbare Weise enthält, ohne dass Sie nach spezifischen Befehlsoptionen suchen müssen.

Kommentare (3)