Solución:
No pude hacer esto:
UPDATE users SET created = NULL WHERE created = '0000-00-00 00:00:00'
(en MySQL 5.7.13).
Seguí recibiendo el Incorrect datetime value: '0000-00-00 00:00:00' error.
Curiosamente, esto funcionó: SELECT * FROM users WHERE created = '0000-00-00 00:00:00'. No tengo idea de por qué falla el primero y el segundo funciona … ¿tal vez un error de MySQL?
En cualquier caso, esta consulta ACTUALIZAR funcionó:
UPDATE users SET created = NULL WHERE CAST(created AS CHAR(20)) = '0000-00-00 00:00:00'
Cambiar el valor predeterminado de una columna con un ALTER TABLE declaración, por ejemplo
ALTER TABLE users MODIFY created datetime NULL DEFAULT '1970-01-02'
… no cambia ningún valor que ya esté almacenado. El valor “predeterminado” se aplica a las filas que se insertan y para las que no se proporciona un valor para la columna.
En cuanto a por qué se encuentra con el error, es probable que el sql_mode la configuración para su sesión incluye NO_ZERO_DATE.
Referencia: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date
Cuando hizo la “importación”, las instrucciones SQL que hicieron la INSERCIÓN en esa tabla se ejecutaron en una sesión que permitía fechas cero.
Para ver la configuración de sql_mode:
SHOW VARIABLES LIKE 'sql_mode' ;
-o-
SELECT @@sql_mode ;
En cuanto a cómo “solucionar” el problema actual, para que el error no se produzca cuando ejecute el ALTER TABLE declaración.
Varias opciones:
1) cambia el sql_mode para permitir fechas cero, eliminando NO_ZERO_DATE y NO_ZERO_IN_DATE. El cambio se puede aplicar en el archivo my.cnf, por lo que después de reiniciar MySQL Server, sql_mode La variable se inicializará con la configuración en my.cnf.
Para un cambio temporal, podemos modificar la configuración con una sola sesión, sin requerir un cambio global.
-- 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 ;
2) cambia el created columna para permitir valores NULL y actualizar las filas existentes para cambiar las fechas cero a valores nulos
3) actualice las filas existentes para cambiar las fechas cero a una fecha válida
No necesitamos ejecutar declaraciones individuales para actualizar cada fila. Podemos actualizar todas las filas de una sola vez (asumiendo que es una tabla de tamaño razonable. Para una tabla más grande, para evitar una enorme generación de deshacer / retroceder, podemos realizar la operación en fragmentos de tamaño razonable).
En la pregunta, el AUTO_INCREMENT El valor mostrado para la definición de la tabla nos asegura que el número de filas no es excesivo.
Si ya hemos cambiado el created columna para permitir NULL valores, podemos hacer algo como esto:
UPDATE `users` SET `created` = NULL WHERE `created` = '0000-00-00 00:00:00'
O podemos establecerlos en una fecha válida, por ejemplo, el 2 de enero de 1970.
UPDATE `users` SET `created` = '1970-01-02' WHERE `created` = '0000-00-00 00:00:00'
(Tenga en cuenta que un valor de fecha y hora de la medianoche del 1 de enero de 1970 ('1970-01-01 00:00:00') es una “fecha cero”. Que será evaluado para ser '0000-00-00 00:00:00'
Lo solucioné haciendo esto antes de la consulta
SET SQL_MODE='ALLOW_INVALID_DATES';