在将某张表进行modify操作的时候总是报这个错误，导致alter失败，select where发现确实是有数据datetime格式并且时间竟然是’0000-00-00 00:00:00’ ，之后查到原因，说是由于sql_mode的缘故限制了datetime字段的默认值不能使zeor，使用SHOW VARIABLES LIKE ‘sql_mode’，查看输出结果是ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION，确实是，使用 set sql_mode = ‘’ 临时将sql_mode将日期格式的限制去掉，之后将该列有问题的数据update未now()问题解决
后来想这个问题可能的原因应该是从5.1版本的mysql导出的sql导入到5.7版本的时候出现的问题，因为这两个版本的默认sql_mode是不一样的所以导致了这个问题，刚才看了一下，5.1版本的mysql sql_mode是空的。
#
The error is because of the sql mode which can be strict mode as per latest MYSQL 5.7 documentation
MySQL Documentation 5.7 says:
> 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.
To Check MYSQL mode
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode sessionDisabling STRICT_TRANS_TABLES mode
However to allow the format
0000-00-00 00:00:00you have to disable STRICT_TRANS_TABLES mode in mysql config file or by commandBy command
SET sql_mode = '';if above is not working than go to
/etc/mysql/my.cnf (as per ubuntu) and comment out STRICT_TRANS_TABLESNote
However strict mode is not enabled by default in MYSQL 5.6. Hence it does not produce the error as per MYSQL 6 documentation which says
> 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
Regarding the bug matter as said by @Dylan-Su:
I don’t think this is the bug it the way MYSQL is evolved over the time due to which some things are changed based on further improvement of the product.
However I have another related bug report regarding the
NOW() functionDatetime field does not accept default NOW()
Another Useful note [see Automatic Initialization and Updating for TIMESTAMP and DATETIME]
> 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.
转载请注明来源链接 http://just4fun.im/2017/01/13/mysql-datetime-incorrect-datetime-value-0000-00-00-000000-for-column/ 尊重知识,谢谢:)