为 DATE 或 DATETIME 设置默认值时 MySQL 出错

Question

我正在运行 MySql Server 5.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'

只是工作。

DATE 的情况相同。

作为 _旁注_，它在 MySQL 文档 中提到：

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 文档的第二个引用，谁能告诉我为什么它会给出这个错误？

原文由 Evhz 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

该错误是因为根据最新的 MYSQL 5.7 文档可以是严格模式的 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

禁用 STRICT_TRANS_TABLES 模式

但是，要允许格式 0000-00-00 00:00:00 您必须在 mysql 配置文件中或通过命令禁用 STRICT_TRANS_TABLES 模式

通过命令

SET sql_mode = '';

或者

SET GLOBAL sql_mode = '';

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

如果上述方法不起作用，请转到 /etc/mysql/my.cnf （根据 ubuntu）并注释掉 STRICT_TRANS_TABLES

此外，如果您想在服务器启动时永久设置 sql 模式，则在 Linux 或 MacOS 上的 my.cnf SET sql_mode='' 。对于 Windows，这必须在 my.ini 文件中完成。

笔记

但是在 MYSQL 5.6 中默认不启用严格模式。因此，它不会根据 MYSQL 6 文档 产生错误，该文档说

MySQL 允许您将“0000-00-00”的“零”值存储为“虚拟日期”。在某些情况下，这比使用 NULL 值更方便，并且使用更少的数据和索引空间。要禁止“0000-00-00”，请启用 NO_ZERO_DATE SQL 模式。

更新

关于@Dylan-Su 所说的错误问题：

我不认为这是 MYSQL 随着时间的推移而演变的错误，由于产品的进一步改进，一些事情发生了变化。

但是我有另一个关于 NOW() 函数的相关错误报告

日期时间字段不接受默认的 NOW()

另一个有用的说明[参见 TIMESTAMP 和 DATETIME 的自动初始化和更新]

从 MySQL 5.6.5 开始，TIMESTAMP 和 DATETIME 列可以自动初始化并更新为当前日期和时间（即当前时间戳）。在 5.6.5 之前，这仅适用于 TIMESTAMP，并且每个表最多有一个 TIMESTAMP 列。以下注释首先描述了 MySQL 5.6.5 及更高版本的自动初始化和更新，然后描述了 5.6.5 之前版本的差异。

关于 NO_ZERO_DATE 的更新

自 MySQL 5.7.4 起，此模式已弃用。对于以前的版本，您必须注释掉配置文件中的相应行。请参阅 关于 NO_ZERO_DATE 的 MySQL 5.7 文档

原文由 geeksal 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

我正在做一个新项目，试图添加一个新列

ALTER TABLE prefix_example_table ADD COLUMN stackoverflow decimal(11,3) NOT NULL

并得到了这个错误

#1292 - Incorrect date value: '0000-00-00'

对于我什至没有尝试向其添加新数据的另一列，返回此错误的列具有 Date 类型的默认值 “0000-00-00” 。

正如@geeksal 提到的

严格模式影响服务器是否允许“0000-00-00”作为有效日期：如果未启用严格模式，则允许“0000-00-00”并且插入不会产生警告。如果启用了严格模式，则不允许使用“0000-00-00”并且插入会产生错误，除非也给出了 IGNORE。对于 INSERT IGNORE 和 UPDATE IGNORE，允许使用“0000-00-00”，并且插入会产生警告。

我已经尝试了几个小时，认为问题与 特权 有关

这节省了我的工作时间。

 SET GLOBAL sql_mode = '';

原文由 WardNsour 发布，翻译遵循 CC BY-SA 4.0 许可协议