当前位置:   article > 正文

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

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'

  1. alert table order_info modify update_time datetime not null default '0000-00-00 00:00:00';
  2. alert table order_info modify send_time datetime not null default '0000-00-00 00:00:00';
  3. 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同时修改三个字段
  1. alert table order_info modify update_time datetime not null default '2000-01-01 00:00:00',
  2. modify send_time datetime not null default '2000-01-01 00:00:00';
  3. 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 模式中的哪一个(如果有的话);

数据类型“零”值
DATE'0000-00-00'
TIME'00:00:00'
DATETIME'0000-00-00 00:00:00'
TIMESTAMP'0000-00-00 00:00:00'
YEAR0000

在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`值来表示未知或未指定的日期时间更为合适。 

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/笔触狂放9/article/detail/607251
推荐阅读
相关标签
  

闽ICP备14008679号