【bug】[42000][1067] Invalid default value for ‘xxx_time‘

MySQL错误解决：Invalid default value for 'xxx_time’问题分析与修复方案

问题描述

在MySQL数据库操作中，当尝试创建或修改表结构时，可能会遇到以下错误信息：

[bug] [42000][1067] Invalid default value for 'xxx_time'

这个错误通常发生在处理时间/日期类型字段时，特别是当系统设置了严格的SQL模式（SQL Mode）时。

错误原因分析

该错误的根本原因在于MySQL的SQL模式中启用了NO_ZERO_DATE和NO_ZERO_IN_DATE选项。这些选项是MySQL严格模式的一部分，它们：

1. NO_ZERO_DATE：禁止使用’0000-00-00’作为合法日期
2. NO_ZERO_IN_DATE：禁止月份或日期部分为零的日期（如’2021-00-01’或’2021-01-00’）

当这些模式启用时，MySQL会拒绝接受"零值"作为日期/时间字段的默认值，从而抛出1067错误。

诊断步骤

1. 首先检查当前MySQL服务器的SQL模式设置：

SHOW VARIABLES LIKE 'sql_mode';

典型的严格模式设置可能返回如下结果：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

解决方案

临时解决方案（会话级别）

对于当前会话，可以通过修改SQL模式来临时解决问题：

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

这条命令移除了NO_ZERO_DATE和NO_ZERO_IN_DATE限制，但保留了其他重要的严格模式选项。

永久解决方案（全局级别）

如果需要永久性修改，可以在MySQL配置文件（通常是my.cnf或my.ini）中添加或修改以下行：

[mysqld]
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

然后重启MySQL服务使更改生效。

注意事项

1. 移除NO_ZERO_DATE和NO_ZERO_IN_DATE限制后，系统将允许使用零值日期，这可能导致数据完整性问题
2. 更规范的解决方案是修改表结构，为时间字段设置合理的默认值（如CURRENT_TIMESTAMP）
3. 在生产环境中修改SQL模式前，应充分评估对现有应用的影响

最佳实践建议

1. 对于新项目，建议在设计阶段就明确定义所有时间字段的合理默认值
2. 如果必须使用零值日期，应在应用层进行明确的业务逻辑处理
3. 定期检查数据库中的异常日期值，确保数据质量