mysql修改表结构报错：1067 - Invalid default value for ‘字段名‘_1067 - invalid default value for 'update time

一、背景

要求根据规范，将已有库中日期字段timestamp类型修改为datetime类型。操作过程中有某些表报错1067 - Invalid default value for 'UPDATE_TIME'。

数据库版本：MySQL8.0.24

二、问题排查

发现表order_info中有三个字段create_time、update_time、send_time中都使用了timestamp类型，其中update_time、send_time设置了默认值为'0000-00-00 00:00:00',create_time设置了默认值为current_timestamp。

当使用下列语句逐个字段修改时报错 1067 - Invalid default value for 'update_time'

 
alert table order_info modify update_time datetime not null default '0000-00-00 00:00:00';
 
alert table order_info modify send_time datetime not null default '0000-00-00 00:00:00';
 
alert table order_info modify create_time datetime not null default current_timestamp on uodate current_timestamp;

三、解决方法

1. 修改默认值为'2000-01-01 00:00:00'
2. 一条sql同时修改三个字段

alert table order_info modify update_time datetime not null default '2000-01-01 00:00:00',
modify send_time datetime not null default '2000-01-01 00:00:00';
create_time datetime not null default current_timestamp on uodate current_timestamp;

四、问题原因

在MySQL 8中，直接为`TIMESTAMP`列设置默认值为`'0000-00-00 00:00:00'`会导致错误，因为这个日期时间值被认为是无效的，并且MySQL的严格模式禁止使用这种零日期。MySQL 5.7及更高版本中，默认的SQL_MODE包含`NO_ZERO_DATE`和`NO_ZERO_IN_DATE`，这会阻止存储零日期和零时间。 如果你确实需要一个"空"或无效的默认值表现，一个替代方案是让`TIMESTAMP`列允许`NULL`值，并且不设置默认值。当没有明确赋值时，它将默认为`NULL`。这是符合SQL标准且避免了无效日期的问题。

因为是要求是改字段，并不想调整库设置，所以用'2000-01-01 00:00:00'替换了'0000-00-00 00:00:00'（系统2020年才开始开发）。并且表中有两处设置了改默认值，只能一条sql去同时修改。

如果调整数据库的sql_mode后，重启数据库，用原来的修改语句没有问题。

参考内容：

DATETIME类型用于包含日期和时间部分的值。MySQL 以格式检索和显示 DATETIME值 。支持的范围是 到. 'YYYY-MM-DD hh:mm:ss''1000-01-01 00:00:00''9999-12-31 23:59:59'

TIMESTAMP数据类型用于包含日期和时间部分的值。 TIMESTAMP具有'1970-01-01 00:00:01'UTC 到'2038-01-19 03:14:07'UTC 的范围

该 DEFAULT子句还可用于指定常量（非自动）默认值（例如， DEFAULT 0or DEFAULT '2000-01-01 00:00:00'）。

• MySQL 不接受TIMESTAMP在日或月列中包含零的值或不是有效日期的值。此规则的唯一例外是特殊的“零”值 '0000-00-00 00:00:00'，前提是 SQL 模式允许此值。精确的行为取决于是否 NO_ZERO_DATE启用了严格 SQL 模式和 SQL 模式中的任何一个；

默认情况下，当 MySQL 遇到日期或时间类型的值超出范围或对该类型无效时，它会将值转换为该类型的“零”值。

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

表显示了每种类型的“零” 值的格式。“零”值很特殊，但您可以使用表中显示的值显式存储或引用它们。您也可以使用 更容易编写的值'0'或来执行此操作。0对于包含日期部分的时间类型DATE， DATETIME使用 TIMESTAMP这些值可能会产生警告或错误。精确的行为取决于 NO_ZERO_DATE启用了严格模式和 SQL 模式中的哪一个（如果有的话）；

在MySQL中，默认情况下，存储零日期（如`'0000-00-00'`）和零时间（如`'00:00:00'`）是被禁止的，因为这些值被认为是无效的。尤其是对于`DATE`和`DATETIME/TIMESTAMP`类型，MySQL在严格的SQL模式下（如启用了`NO_ZERO_DATE`和`NO_ZERO_IN_DATE`模式）会阻止这类插入。 然而，如果你确实需要存储零日期或时间，你可以通过调整MySQL的SQL模式来允许这些值。具体步骤如下： 1. **关闭严格模式**：可以在MySQL配置文件（如`my.cnf`或`my.ini`）中，或在启动MySQL服务时通过命令行参数，调整`sql_mode`，移除`NO_ZERO_DATE`和`NO_ZERO_IN_DATE`。例如，在配置文件中添加或修改： ```ini [mysqld] sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ``` 这里去掉了原本包含的`NO_ZERO_DATE`和`NO_ZERO_IN_DATE`。 2. **会话级别调整**：你也可以在单个会话中临时改变SQL模式，而不影响全局设置： ```sql SET SESSION sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; ``` **风险与考虑**： 1. **数据完整性受损**：允许零日期和时间可能导致数据库中的无效或模糊数据增加，这可能会使得数据分析变得复杂，难以确定记录的真实时间信息。 2. **应用程序兼容性问题**：一些数据库工具、ORM框架或应用程序可能期望日期时间字段遵循标准的有效值，允许零日期可能引起这些工具的错误或异常。 3. **逻辑错误**：在依赖于日期时间进行计算或过滤的应用场景中，零日期可能导致逻辑错误，比如在计算年龄、时间差或进行日期比较时出现问题。 4. **维护困难**：随着时间推移，零日期的使用可能会导致数据清理、迁移或升级过程中的额外复杂性。 因此，尽管技术上可行，允许存储零日期和时间通常不是最佳实践，除非有非常具体的业务需求并且充分评估了潜在风险。在大多数情况下，使用`NULL`值来表示未知或未指定的日期时间更为合适。