为 DATE 或 DATETIME 设置默认值时 MySQL 中的错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36374335/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error in MySQL when setting default value for DATE or DATETIME
提问by Evhz
I'm running MySql Server 5.7.11 and this sentence:
我正在运行 MySql Server 5.7.11 和这句话：
updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
is notworking. 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.
DATE 的情况相同。
As a sidenote, it is mentioned in the MySQL docs:
作为旁注，它在MySQL 文档中提到：
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 类型用于具有日期部分但没有时间部分的值。MySQL 以 'YYYY-MM-DD' 格式检索和显示 DATE 值。支持的范围是“1000-01-01”到“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').
无效的 DATE、DATETIME 或 TIMESTAMP 值被转换为适当类型的“零”值（'0000-00-00' 或 '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?
还考虑到 MySQL 文档中的第二个引用，谁能告诉我为什么它会给出该错误？
回答by geeksal
The error is because of the sql mode which can be strict mode as per latest MYSQL 5.7 documentation
该错误是由于 sql 模式，根据最新的 MYSQL 5.7 文档，该模式可以是严格模式
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.
严格模式会影响服务器是否允许 '0000-00-00' 作为有效日期：如果未启用严格模式，则允许使用 '0000-00-00' 并且插入不会产生警告。如果启用了严格模式，则不允许使用 '0000-00-00' 并且插入会产生错误，除非也给出了 IGNORE。对于 INSERT IGNORE 和 UPDATE IGNORE，允许使用 '0000-00-00' 并且插入会产生警告。
To Check MYSQL mode
检查MYSQL模式
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session
Disabling STRICT_TRANS_TABLES mode
禁用 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
但是，要允许0000-00-00 00:00:00您必须在 mysql 配置文件中或通过命令禁用 STRICT_TRANS_TABLES 模式的格式
By command
通过命令
SET sql_mode = '';
SET sql_mode = '';
or
或者
SET GLOBAL sql_mode = '';
SET GLOBAL sql_mode = '';
Using the keyword GLOBALrequires super previliges and it affects the operations all clients connect from that time on
使用关键字GLOBAL需要超级特权，它会影响从那时起所有客户端连接的操作
if above is not working than go to /etc/mysql/my.cnf(as per ubuntu) and comment out STRICT_TRANS_TABLES
如果以上不起作用，请转到/etc/mysql/my.cnf（根据 ubuntu）并注释掉STRICT_TRANS_TABLES
Also, if you want to permanently set the sql mode at server startup then include SET sql_mode=''in my.cnfon Linux or MacOS. For windows this has to be done in my.inifile.
另外，如果你想永久设置在服务器启动时的SQL模式，然后包括SET sql_mode=''在my.cnfLinux或MacOS的。对于 Windows，这必须在my.ini文件中完成。
Note
笔记
However strict mode is not enabled by default in MYSQL 5.6. Hence it does not produce the error as per MYSQL 6 documentationwhich says
然而，在 MYSQL 5.6 中默认不启用严格模式。因此，它不会根据MYSQL 6 文档产生错误，其中说
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.
MySQL 允许您将“0000-00-00”的“零”值存储为“虚拟日期”。这在某些情况下比使用 NULL 值更方便，并且使用更少的数据和索引空间。要禁止“0000-00-00”，请启用 NO_ZERO_DATE SQL 模式。
UPDATE
更新
Regarding the bug matter as said by @Dylan-Su:
关于@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.
我不认为这是 MYSQL 随时间演变的方式的错误，因为根据产品的进一步改进，某些事情发生了变化。
However I have another related bug report regarding the NOW()function
但是我有另一个有关该NOW()功能的相关错误报告
Datetime field does not accept default NOW()
Another Useful note[see Automatic Initialization and Updating for TIMESTAMP and DATETIME]
另一个有用的说明[请参阅TIMESTAMP 和 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.
从 MySQL 5.6.5 开始，TIMESTAMP 和 DATETIME 列可以自动初始化并更新为当前日期和时间（即当前时间戳）。在 5.6.5 之前，这仅适用于 TIMESTAMP，并且每个表最多有一个 TIMESTAMP 列。下面的注释首先描述了 MySQL 5.6.5 及更高版本的自动初始化和更新，然后是 5.6.5 之前版本的差异。
Update Regarding NO_ZERO_DATE
关于 NO_ZERO_DATE 的更新
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
从 MySQL 5.7.4 开始，不推荐使用此模式。对于以前的版本，您必须注释掉配置文件中的相应行。请参阅关于 NO_ZERO_DATE 的 MySQL 5.7 文档
回答by bg17aw
I had this error with WAMP 3.0.6 with MySql 5.7.14.
我在 WAMP 3.0.6 和 MySql 5.7.14 上遇到了这个错误。
Solution:
解决方案：
change line 70 (if your ini file is untouched) in c:\wamp\bin\mysql\mysql5.7.14\my.inifile from
将文件中的第 70 行（如果您的 ini 文件未修改） c:\wamp\bin\mysql\mysql5.7.14\my.ini从
sql-mode= "STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"
to
到
sql-mode="ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"
and restart all services.
并重启所有服务。
This will disable strict mode. As per the documentation, “strict mode” means a mode with either or both STRICT_TRANS_TABLESor STRICT_ALL_TABLESenabled.
The documentationsays:
这将禁用严格模式。具体根据文档中，“严格模式”指的是模式之一或两者STRICT_TRANS_TABLES或STRICT_ALL_TABLES启用。该文件说：
"The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION."
“MySQL 5.7 中的默认 SQL 模式包括这些模式：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。”
回答by Martin T.
I got into a situation where the data was mixed between NULL and 0000-00-00 for a date field. But I did not know how to update the '0000-00-00' to NULL, because
我遇到了数据在日期字段的 NULL 和 0000-00-00 之间混合的情况。但我不知道如何将 '0000-00-00' 更新为 NULL，因为
update my_table set my_date_field=NULL where my_date_field='0000-00-00'
is not allowed any more. My workaround was quite simple:
不允许了。我的解决方法很简单：
update my_table set my_date_field=NULL where my_date_field<'1000-01-01'
because all the incorrect my_date_fieldvalues (whether correct dates or not) were from before this date.
因为所有不正确的my_date_field值（无论日期是否正确）都来自此日期之前。
回答by Heroselohim
Config syntax issue
配置语法问题
On some versions of MYSQL (tested 5.7.*) under *nix systems you should use this syntax:
在 *nix 系统下的某些 MYSQL 版本（已测试 5.7.*）上，您应该使用以下语法：
[mysqld]
sql-mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_ENGINE_SUBSTITUTION"
These won't work:
这些将不起作用：
dash no quotes
破折号没有引号
sql-mode=NO_ENGINE_SUBSTITUTION
underscore no quotes
下划线没有引号
sql_mode=NO_ENGINE_SUBSTITUTION
underscore and quotes
下划线和引号
sql_mode="NO_ENGINE_SUBSTITUTION"
A more complete review of config values and sql-mode:
对配置值和 sql-mode 的更完整回顾：
回答by simhumileco
First select current session sql_mode:
首先选择当前会话sql_mode：
SELECT @@SESSION.sql_mode;
Then you will get something like that default value:
然后你会得到类似默认值的东西：
'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'
'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'
and then set sql_modewithout 'NO_ZERO_DATE':
然后设置sql_mode没有'NO_ZERO_DATE'：
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
If you have grants, you can do it also for GLOBAL:
如果您有赠款，您也可以为GLOBAL：
SELECT @@GLOBAL.sql_mode;
SET GLOBAL sql_mode = '...';
回答by ferreidon aftahi
Just add the line: sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
只需添加以下行： sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
inside file: /etc/mysql/mysql.conf.d/mysqld.cnf
内部文件： /etc/mysql/mysql.conf.d/mysqld.cnf
then sudo service mysql restart
然后 sudo service mysql restart
回答by Dylan Su
It works for 5.7.8:
它适用于 5.7.8：
mysql> create table t1(updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00');
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc |
+-----------+
1 row in set (0.00 sec)
You can create a SQLFiddle to recreate your issue.
您可以创建一个 SQLFiddle 来重新创建您的问题。
If it works for MySQL 5.6 and 5.7.8, but fails on 5.7.11. Then it probably be a regression bug for 5.7.11.
如果它适用于 MySQL 5.6 和 5.7.8，但在 5.7.11 上失败。那么它可能是 5.7.11 的回归错误。
回答by Vindic
To solve the problem with MySQL Workbench (After applying the solution on the server side) :
使用 MySQL Workbench 解决问题（在服务器端应用该解决方案后）：
Remove SQL_MODE to TRADITIONAL in the preferences panel.
在首选项面板中将 SQL_MODE 删除为 TRADITIONAL。
回答by stackdave
This answer it's just for MySQL 5.7:
这个答案仅适用于 MySQL 5.7：
Best is not really set in blank the sql_mode, instead use in PHP a session variable with:
Best 并没有真正将 sql_mode 设置为空白，而是在 PHP 中使用会话变量：
SET SESSION sql_mode= 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
So at least you keep the another default values.
所以至少你保持另一个默认值。
It's crazy that mysql documentation is not clear, you need delete this defeault values in sql_mode:
mysql文档不清楚，你需要在sql_mode中删除这个默认值：
NO_ZERO_IN_DATE,NO_ZERO_DATE, I understand, but in the future versiones this will be discontinued.
NO_ZERO_IN_DATE，NO_ZERO_DATE，我明白，但在未来的版本中这将停止。
STRICT_ALL_TABLES, with this, before parameters will be ignored, so you need delete it too.
STRICT_ALL_TABLES，有了这个，之前的参数将被忽略，所以你也需要删除它。
Finally TRADITIONAL too, but documentation speak about this parameter: “give an error instead of a warning” when inserting an incorrect value into a column", with this parameter, dates with zero values is not inserted, but without yes.
最后也是 TRADITIONAL，但文档谈到了这个参数：“在向列中插入不正确的值时给出错误而不是警告”，使用此参数，不会插入具有零值的日期，但没有插入。
MySQL is not really organised with these parameters and combinations.
MySQL 并没有真正用这些参数和组合来组织。
回答by Green
Option combinations for mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64).
的选项组合mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64)。
Doesn't throw:
不扔：
STRICT_TRANS_TABLES+ NO_ZERO_DATE
STRICT_TRANS_TABLES+ NO_ZERO_DATE
Throws:
抛出：
STRICT_TRANS_TABLES+ NO_ZERO_IN_DATE
STRICT_TRANS_TABLES+ NO_ZERO_IN_DATE
My settings in /etc/mysql/my.cnfon Ubuntu:
我在/etc/mysql/my.cnfUbuntu 上的设置：
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
