在设置DATE或DATETIME的默认值时出现mysql错误 [英] Error in mysql when setting default value for DATE or DATETIME
问题描述
我正在运行MySql Server 5.7.11和这句话：
更新datetime NOT NULL DEFAULT'0000-00- 00 00:00:00'
是不工作。给出错误：
错误1067（42000）：'updated'的默认值无效
但是如下：
NOT NULL DEFAULT'1000-01-01 00:00:00'
只是工作。
与DATE相同的情况。
作为 sidenote 在 mysql docs 中：
DATE类型用于具有日期部分但没有时间部分的值。 MySQL以YYYY-MM-DD格式检索并显示DATE值。支持的范围是1000-01-01到9999-12-31。
即使他们也说：
DATE，DATETIME或TIMESTAMP值无效转换为相应类型的零值（0000-00-00或0000-00-00 00:00:00）。
还考虑了mysql文档的第二个引用，有谁能让我知道为什么会发出这个错误？
错误是因为sql模式可以根据最新的MYSQL 5.7文档严格模式
严格模式影响服务器是否允许0000-00-00作为有效日期：
如果严格模式未启用，允许0000-00-00，插入不产生警告。
如果启用严格模式，则不允许0000-00-00，否则插入会产生错误，除非给出了IGNORE。对于INSERT IGNORE和UPDATE IGNORE，允许'0000-00-00'，插入会产生警告。
要检查MYSQL模式
SELECT @@ GLOBAL.sql_mode global，@@ SESSION.sql_mode session p>
禁用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文档产生错误这说明
MySQL允许您将0000-00-00的零值存储为虚拟日期。在某些情况下比使用NULL值更方便，并且使用较少的数据和索引空间。要禁止0000-00-00，请启用NO_ZERO_DATE SQL模式。
更新
正如@ Dylan-Su所说：
我不认为这是MYSQL在时间上演变的错误，因为有些事情根据
然而，我有另一个相关的错误报告，关于 NOW（） function
另一个有用的注释 [参见 TIMESTAMP和DATETIME的自动初始化和更新]
从MySQL 5.6.5开始，TIMESTAMP和DATETIME列可以自动初始化并更新为当前日期和时间（即当前时间戳）。在5.6.5之前，这只适用于TIMESTAMP，每个表最多只有一个TIMESTAMP列。以下注意事项首先描述MySQL 5.6.5及更高版本的自动初始化和更新，然后介绍5.6.5之前版本的差异。
I'm running MySql Server 5.7.11 and this sentence:
updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
is not working. Giving the error:
ERROR 1067 (42000): Invalid default value for 'updated'
But the following:
updated datetime NOT NULL DEFAULT '1000-01-01 00:00:00'
just works.
The same case for DATE.
As a sidenote, it is mentioned in the mysql docs:
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'.
even if they also say:
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').
Having also into account the second quote from mysql documentation, could anyone let me know why it is giving that error?
The error is because of the sql mode which can be strict mode as per latest MYSQL 5.7 documentation
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 session
Disabling 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 command
By command
SET sql_mode = '';
if above is not working than go to /etc/mysql/my.cnf (as per ubuntu) and comment out STRICT_TRANS_TABLES
Note
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() function
Datetime 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.
这篇关于在设置DATE或DATETIME的默认值时出现mysql错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
