为 DATE 或 DATETIME 设置默认值时 MySQL 中的错误
我运行的是 MySql Server 5.7.11 和这句话:
I'm running MySql Server 5.7.11 and this sentence:
updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
不起作用.给出错误:
ERROR 1067 (42000): Invalid default value for 'updated'
但是以下:
updated datetime NOT NULL DEFAULT '1000-01-01 00:00:00'
刚刚好.
DATE 的情况相同.
The same case for DATE.
作为旁注，它在MySQL 文档:
DATE 类型用于具有日期部分但没有时间部分的值.MySQL 以 'YYYY-MM-DD' 格式检索和显示 DATE 值.支持的范围是1000-01-01"到9999-12-31".
The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.
即使他们也说:
无效的 DATE、DATETIME 或 TIMESTAMP 值将转换为适当类型的零"值('0000-00-00' 或 '0000-00-00 00:00:00').
Invalid DATE, DATETIME, or TIMESTAMP values are converted to the "zero" value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').
还考虑到 MySQL 文档中的第二个引用，谁能告诉我为什么会出现该错误?
Having also into account the second quote from MySQL documentation, could anyone let me know why it is giving that error?
推荐答案
该错误是由于 sql 模式，根据最新的 MYSQL 5.7 文档可以是严格模式
The error is because of the sql mode which can be strict mode as per latest MYSQL 5.7 documentation
MySQL 文档 5.7 说:
严格模式会影响服务器是否允许0000-00-00"作为有效日期:如果未启用严格模式，则允许使用 '0000-00-00' 并且插入不会产生警告.如果启用了严格模式，则不允许使用 '0000-00-00' 并且插入会产生错误，除非也给出了 IGNORE.对于 INSERT IGNORE 和 UPDATE IGNORE，允许使用0000-00-00"并且插入会产生警告.
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 全局，@@SESSION.sql_mode 会话
禁用 STRICT_TRANS_TABLES 模式
但是要允许格式 0000-00-00 00:00:00您必须在 mysql 配置文件中或通过命令禁用 STRICT_TRANS_TABLES 模式
However to allow the format 0000-00-00 00:00:00you have to disable STRICT_TRANS_TABLES mode in mysql config file or by command
通过命令
SET sql_mode = '';
或
SET GLOBAL sql_mode = '';
使用关键字 GLOBAL 需要超级特权，它会影响从那时起所有客户端连接的操作
Using the keyword GLOBAL requires super previliges and it affects the operations all clients connect from that time on
如果以上不起作用，请转到 /etc/mysql/my.cnf(根据 ubuntu)并注释掉 STRICT_TRANS_TABLES
if above is not working than go to /etc/mysql/my.cnf (as per ubuntu) and comment out STRICT_TRANS_TABLES
此外，如果您想在服务器启动时永久设置 sql 模式，请在 Linux 或 MacOS 上的 my.cnf 中包含 SET sql_mode=''.对于 Windows，这必须在 my.ini 文件中完成.
Also, if you want to permanently set the sql mode at server startup then include SET sql_mode='' in my.cnf on Linux or MacOS. For windows this has to be done in my.ini file.
注意
然而，在 MYSQL 5.6 中默认没有启用严格模式.因此它不会按照 MYSQL 6 产生错误文档其中说
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 允许您将0000-00-00"的零"值存储为虚拟日期".这在某些情况下比使用 NULL 值更方便，并且使用更少的数据和索引空间.要禁止0000-00-00"，请启用 NO_ZERO_DATE SQL 模式.
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.
更新
关于@Dylan-Su 所说的错误问题:
Regarding the bug matter as said by @Dylan-Su:
我不认为这是 MYSQL 随着时间的推移演变的方式的错误，因为根据产品的进一步改进，某些事情发生了变化.
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.
但是我有另一个关于 NOW() 函数的相关错误报告
However I have another related bug report regarding the NOW() function
日期时间字段不接受默认的 NOW()
另一个有用的说明 [参见 TIMESTAMP 和 DATETIME 的自动初始化和更新]
从 MySQL 5.6.5 开始，TIMESTAMP 和 DATETIME 列可以自动初始化并更新为当前日期和时间(即当前时间戳).在 5.6.5 之前，这仅适用于 TIMESTAMP，并且每个表最多有一个 TIMESTAMP 列.下面的注释首先描述了 MySQL 5.6.5 及更高版本的自动初始化和更新，然后是 5.6.5 之前版本的差异.
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.
关于 NO_ZERO_DATE 的更新
自 MySQL 5.7.4 起，此模式已弃用.对于以前的版本，您必须注释掉配置文件中的相应行.请参阅 关于 NO_ZERO_DATE 的 MySQL 5.7 文档
As of MySQL as of 5.7.4 this mode is deprecated. For previous version you must comment out the respective line in the config file. Refer MySQL 5.7 documentation on NO_ZERO_DATE
相关文章