在为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'的默认值无效
 
 更新的datetime NOT NULL DEFAULT'1000-01-01 00:00:00'
  
  / strong>。 
 
 
 
 DATE的情况相同。
 
 
 
作为 sidenote 在 mysql文档中：
 
 
 < blockquote> 
 
 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文档
 
 
 
  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模式。 
 
 
  UPDATE  
 
 
 
正如@ Dylan-Su所说：
 
 
 
我不认为这是MYSQL的发展方式的错误，因为一些事情是基于进一步改进产品。
 
 
 
但我有另一个相关的错误报告关于 NOW（）函数
 
 
 
 日期时间字段不接受默认值NOW（） 
 
 
 
 另一个有用的注意事项 [see 自动初始化和更新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

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 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屋！