mysql date设置出错_在设置DATE或DATETIME的默认值时出现mysql错误

错误是因为sql模式可以根据最新的MYSQL 5.7文档严格模式

Strict mode affects whether the server permits ‘0000-00-00’ as a valid date:

If strict mode is not enabled, ‘0000-00-00’ is permitted and inserts produce no warning.

If strict mode is enabled, ‘0000-00-00’ is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, ‘0000-00-00’ is permitted and inserts produce a warning.

检查MYSQL模式

SELECT @@ GLOBAL.sql_mode global，@@ SESSION.sql_mode会话

禁用STRICT_TRANS_TABLES模式

但是要允许格式0000-00-00 00：00：00你必须在mysql配置文件或命令中禁用STRICT_TRANS_TABLES模式

按命令

SET sql_mode =”;

如果上面的工作不是去/etc/mysql/my.cnf(根据ubuntu)并注释掉STRICT_TRANS_TABLES

注意

但是，严格模式在MYSQL 5.6中默认不启用。因此，它不会产生根据MYSQL 6 documentation的错误

MySQL permits you to store a “zero” value of ‘0000-00-00’ as a “dummy date.” This is in some cases more convenient than using NULL values, and uses less data and index space. To disallow ‘0000-00-00’, enable the NO_ZERO_DATE SQL mode.

UPDATE

关于@ Dylan-Su所说的bug问题：

我不认为这是MYSQL在时间上演变的错误，因为有些事情根据产品的进一步改进而改变。

但是我有另一个有关NOW()函数的相关错误报告

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table. The following notes first describe automatic initialization and updating for MySQL 5.6.5 and up, then the differences for versions preceding 5.6.5.