mysql datetime 为空报错吗_关于sql：为DATE或DATETIME设置默认值时mysql出错

我运行的是MySQLServer5.7.11，这句话是：

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'

只是工作而已。

同样的情况。

作为旁注，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'.

号

即使他们也说：

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文档中的第二个引用，有人能告诉我为什么会出现这种错误吗？

为什么你想要一个显然毫无意义的违约？如果日期未知，那么这正是NULL的目的。

注意：这在5.6版的SQL fiddle中有效——sqlpiddle.com/！9/02C98。

@卡洛斯检查更新的答案。

错误是因为SQL模式，根据最新的MySQL5.7文档，该模式可以是严格模式。

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.

号

检查mysql模式

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session。

禁用严格传输表模式

但是，要允许使用0000-00-00 00:00:00格式，必须在mysql配置文件中或通过命令禁用严格的trans-tables模式。

按命令

埃多克斯1〔2〕

或

埃多克斯1〔3〕

使用关键字GLOBAL需要超级previlions，它会影响从那时起所有客户端连接的操作。

如果上述不起作用，则转到/etc/mysql/my.cnf(根据ubuntu)并评论STRICT_TRANS_TABLES。

另外，如果要在服务器启动时永久设置SQL模式，那么在Linux或MacOS上的my.cnf中包括SET sql_mode=''。对于Windows，这必须在my.ini文件中完成。

注释

但是，在MySQL5.6中，默认情况下不启用严格模式。因此，根据MySQL6文档，它不会产生错误

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所说的bug问题：

我不认为这是一个bug，它是MySQL随着时间的推移而发展的方式，因为有些东西是根据产品的进一步改进而改变的。

但是，我有另一个关于NOW()功能的相关错误报告。

日期时间字段现在不接受默认值()。

另一个有用的注释[请参见时间戳和日期时间的自动初始化和更新]

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.

号

关于无零日期的更新

从5.7.4开始的MySQL，不推荐使用此模式。对于以前的版本，您必须注释掉配置文件中的相应行。参考MySQL5.7文档

更新忽略是我要找的？？？？？

设置sql_mode=''；工作

错了。我的mysql实例(本地实例和服务器实例)都有STRICT_TRANS_TABLES。但是，我可以很容易地在本地实例中插入0000-00-00，但不能在服务器实例中插入—抛出错误。为什么？因为我的服务器mysql配置启用了NO_ZERO_DATE。我的本地没有。

好的@green我会找出答案并更新(如果适用)

在我的例子中，"set sql_mode="'不起作用。set global sql_mode="；"为我完成了任务。

还应删除NO_ZERO_DATE。

谢谢@arjenstens，您的问题已经解决了

谢谢@preshanpradeepa，您的问题已得到解决

我不得不在Ubuntu16.04上使用SET sql_mode = '';和SET GLOBAL sql_mode = '';，MySQL5.7.24才能工作。

我在WAMP3.0.6和MySQL5.7.14中遇到了这个错误。

解决方案：

将c:\wamp\bin\mysql\mysql5.7.14\my.ini文件中的第70行(如果您的ini文件未动过)从

sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

号

到

sql-mode="ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

重新启动所有服务。

这将禁用严格模式。根据文件，"严格模式"是指启用或同时启用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."

号

我遇到了这样一种情况：日期字段的数据混合在空值和0000-00-00之间。但我不知道如何将"0000-00-00"更新为空，因为

update my_table set my_date_field=NULL where my_date_field='0000-00-00'

。

不再允许。我的解决方法很简单：

update my_table set my_date_field=NULL where my_date_field

因为所有不正确的my_date_field值(无论日期是否正确)都是在此日期之前开始的。

完美的快速解决方案。实际上，您也可以使用，因为它当然是一个有效日期。

加上一行：sql_mode ="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"。

内部文件：/etc/mysql/mysql.conf.d/mysqld.cnf。

然后是sudo service mysql restart。

适用于5.7.23。

我可能会建议江户一号〔6〕首先，他们从它给你的东西中去掉零日期、零日期和严格的跨表。这样就可以保留已启用的其他设置。我为我的SQL模式设置了不止这两个项目。不知道他们都在做什么，但我不想冒险在这一点上移除它们。

配置语法问题

在*nix系统下的某些MySQL版本(测试5.7.*)上，应使用以下语法：

[mysqld]

sql-mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_ENGINE_SUBSTITUTION"

。这些不起作用：

短划线无引号

sql-mode=NO_ENGINE_SUBSTITUTION

下划线无引号

sql_mode=NO_ENGINE_SUBSTITUTION

。

下划线和引号

sql_mode="NO_ENGINE_SUBSTITUTION"

。

对配置值和SQL模式的更完整的检查：

如何设置永久SQL模式标志

首先选择当前会话sql_mode：

然后您将得到类似于默认值的值：

'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'

号

然后设置不带'NO_ZERO_DATE'的sql_mode：

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';

号

如果您有赠款，您也可以为GLOBAL办理：

SELECT @@GLOBAL.sql_mode;

SET GLOBAL sql_mode = '...';

适用于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)

您可以创建一个sqlfiddle来重新创建您的问题。

http://sqlfiddle.com/

如果它适用于MySQL5.6和5.7.8，但在5.7.11失败。那么它可能是5.7.11的回归错误。

这个答案只适用于MySQL5.7：

best在sql_模式下并不是真正设置为空的，而是在php中使用会话变量：

SET SESSION sql_mode= 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

所以至少你保留了另一个默认值。

MySQL文档不清楚真是太疯狂了，您需要在SQL_模式下删除这个失败值：

我明白，没有零日期，没有零日期，但在未来的版本中，这将被终止。

在参数被忽略之前，用这个来严格定义所有的表，所以你也需要删除它。

最后也是传统的，但是文档中提到了这个参数："在向列中插入不正确的值时给出错误而不是警告"，使用这个参数，没有插入值为零的日期，但是没有"是"。

MySQL并没有真正使用这些参数和组合进行组织。

set global sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

。

mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64)的期权组合。

不投掷：

STRICT_TRANS_TABLES+NO_ZERO_DATE。

抛出：

STRICT_TRANS_TABLES+NO_ZERO_IN_DATE。

我在Ubuntu上的/etc/mysql/my.cnf中的设置：

[mysqld]

sql_mode ="STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

。

使用mysql workbench解决问题(在服务器端应用解决方案后)：

在"首选项"面板中将"SQL_模式"删除为"传统"。

。