我正在运行MySql Server 5.7.11，下面这句话:
updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
not美元有效.给出错误:
ERROR 1067 (42000): Invalid default value for 'updated'
但以下几点:
updated datetime NOT NULL DEFAULT '1000-01-01 00:00:00'
just works
DATE也是这样.
作为sidenote，它在MySQL docs中提到:
日期类型用于包含日期部分但不包含时间部分的值.MySQL以"YYYY-MM-DD"格式检索并显示日期值.支持的范围为"1000-01-01"到"9999-12-31".
即使他们也会说:
无效的日期、日期时间或时间戳值被转换为相应类型的"零"值("0000-00-00"或"0000-00-00 00 00:00").
考虑到MySQL文档中的第二句话，有人能告诉我为什么会出现这个错误吗？
错误是因为sql模式，根据最新的MYSQL 5.7文档，sql模式可能是严格模式
严格模式会影响服务器是否允许"0000-00-00"作为有效日期:
To Check MYSQL mode
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session
Disabling STRICT_TRANS_TABLES mode
但是，要允许使用0000-00-00 00:00:00格式，必须在mysql配置文件中或通过命令禁用STRICT_TRANS_TABLES模式
By command
SET sql_mode = '';
或
SET GLOBAL sql_mode = '';
Using the keyw或d GLOBAL requires super previliges and it affects the operations all clients connect from that time on
if above is not w或king than go to /etc/mysql/my.cnf (as per ubuntu) and comment out STRICT_TRANS_TABLES
Also, if you want to permanently set the sql mode at server startup then include SET sql_mode='' in my.cnf on Linux 或 MacOS. F或 windows this has to be done in my.ini file.
Note
However strict mode is not enabled by default in MYSQL 5.6. Hence it does not produce the err或 as per MYSQL 6 documentation which says
MySQL permits you to st或e a “zero” value of '0000-00-00' as a “dummy date.” This is in some cases m或e 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随着时间的推移而发展的一种缺陷，因为在产品进一步改进的基础上，有些东西发生了变化.
However I have another related bug rep或t regarding the NOW() function
Datetime field does not accept default NOW()
Another Useful note [see Automatic Initialization and Updating f或 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). Bef或e 5.6.5, this is true only f或 TIMESTAMP, and f或 at most one TIMESTAMP column per table. The following notes first describe automatic initialization and updating f或 MySQL 5.6.5 and up, then the differences f或 versions preceding 5.6.5.
Update Regarding NO_ZERO_DATE
As of MySQL as of 5.7.4 this mode is deprecated. F或 previous version you must comment out the respective line in the config file. Refer MySQL 5.7 documentation on NO_ZERO_DATE