MySQL更新错误：日期时间值不正确。

Question

我们的数据库在许多日期时间和时间戳字段中使用“0000-00-00 00:00:00”作为默认值。然而，MySQL已经决定这些类型的字段只能使用有效的日期或null。

但是，“0000-00-00 00:00:00”值曾经是可以接受的，我们的代码检查了这个值。当我设置一个新服务器时，我会编辑/etc/mysql/mysql.conf.d/mysqld.cnf文件，并在[mysqld]部分添加一行。

sql_mode=NO_ENGINE_SUBSTITUTION

今天我尝试搭建一台新服务器。我在MySQL配置中添加了sql_mode=NO_ENGINE_SUBSTITUTION并重新启动了mysql服务。然而，这台新服务器却只出现了错误。

UPDATE example_table SET active = 1 WHERE example_table_id = 1;
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'my_date_field' at row 1

当然，我可以更新数据库将空值或"有效"默认日期（例如'1970-01-01 00:00:01'）写入，但是这将破坏现有的代码，因为它会检查数据是否等于'0000-00-00 00:00:00'。

附加示例信息：

Server type: MySQL
Server version: 5.7.22-0ubuntu0.16.04.1 - (Ubuntu)
Protocol version: 10
innodb_version: 5.7.22

SELECT my_date_field FROM example_table WHERE active = 1;
+---------------------+
| my_date_field       |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+


DESC example_table;
+------------------------------------+--------------+------+-----+---------------------+
| Field                              | Type         | Null | Key | Default             |
+------------------------------------+--------------+------+-----+---------------------+
| my_date_field                      | datetime     | NO   |     | 0000-00-00 00:00:00 |
| active                             | tinyint(2)   | NO   |     | 1                   |
+------------------------------------+--------------+------+-----+---------------------+

我有其他使用相同版本的MySQL的机器，只需 sql_mode=NO_ENGINE_SUBSTITUTION 就可以工作，我几乎可以考虑将其中一个镜像到这台新机器上，直到代码更新为查找有效日期或null而不是“0000-00-00 00:00:00”。

- BrettARose

---

当我重启一个旧项目时遇到了类似的问题（插入没有错误，但在更新其他字段时更新时出现错误，就像你的情况一样），最后发现我有一个触发器将该列与“”（空字符串）进行比较。当我更改为与“0000-00-00 00:00:00”进行比较时，它起作用了。（这是在没有sql_mode中的NO_ZERO_DATE的情况下） - marekful

Answer 1

NO_ENGINE_SUBSTITUTION SQL模式与日期无关。它之所以对你起作用，只是因为通过设置它，你也删除了服务器的默认模式，在现代版本中，这个默认模式是类似于TRADITIONAL的组合模式，其中包括NO_ZERO_IN_DATE和NO_ZERO_DATE等。

你可能只想在会话级别上设置一个旧版模式，例如：

SET @@SESSION.sql_mode = '';

- Álvaro González