参考来源与致谢
- 引用来源:ERROR 1067 (42000): Invalid default value for 'created_at'
- 原作者:原页面署名作者或发布机构(剪藏记录未保存可核验的个人署名)
- 感谢词:感谢原作者及发布机构对技术知识的公开分享。本文已结合企业运维场景完成脱敏、核验与原创重写,并非原文转载;如权利人需要更正署名或来源信息,请联系本站。
适用场景 #
在 MySQL 或兼容分支上执行 ALTER TABLE 新增列、修改列或导入建表脚本时,出现如下报错:
ERROR 1067 (42000): Invalid default value for 'created_at'
典型情况是表中已有 created_at、updated_at 这类时间列,其默认值写法与当前会话或全局 sql_mode 不兼容。即使本次操作只是新增一个无关列,MySQL 仍会重新校验整张表的列定义,因此旧的时间列定义会触发该错误。
本文适用于自建或托管的 MySQL 实例,具体语法与行为请以所用版本的官方文档为准。
准备与风险 #
- 确认数据库版本与当前
sql_mode,不同版本默认值不同,不要照搬他处配置。 - 在生产环境执行任何
ALTER TABLE前,先做逻辑备份或物理备份,并确认备份可恢复。 - 先在测试环境复现并验证,再在生产执行。
- 修改全局
sql_mode会影响该实例上所有新连接,可能放宽日期校验,属于有风险操作,需评估后再做。 - 大表执行
ALTER TABLE可能长时间锁表或占用大量 IO,建议在低峰期进行。
处理步骤 #
1. 查看当前 sql_mode #
SHOW VARIABLES LIKE 'sql_mode';
同时确认会话级与全局级取值,二者可能不同。
2. 判断冲突来源 #
若结果中包含 NO_ZERO_DATE 或 NO_ZERO_IN_DATE,而 created_at 的默认值写成 '0000-00-00 00:00:00' 或 0,即会触发报错。查看表结构:
SHOW CREATE TABLE [表名];
3. 优先修正列定义 #
推荐做法是让时间列使用合法默认值,而不是放宽 sql_mode:
ALTER TABLE [表名]
MODIFY created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE [表名]
MODIFY updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
若业务允许为空,也可改为 NULL DEFAULT NULL。具体可用的默认值写法以对应版本文档为准。
4. 临时调整 sql_mode(仅限必要场景) #
若确需临时放宽,可只移除日期相关项,保留其他严格模式:
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
会话级设置只影响当前连接,连接断开即失效。全局级修改需谨慎,且应记录变更以便回退。
5. 导入脚本场景 #
若为导入 SQL 文件触发,可在文件开头加入会话级 SET 语句,导入完成后连接关闭即恢复,不建议写入全局配置。
验证方法 #
- 重新执行原
ALTER TABLE语句,确认不再报 1067。 - 执行
SHOW CREATE TABLE [表名];,核对时间列默认值与是否允许为空。 - 插入一条测试数据,确认
created_at、updated_at按预期自动填充。 - 再次执行
SHOW VARIABLES LIKE 'sql_mode';,确认改动范围符合预期。
回退建议 #
- 若修改了列定义导致业务异常,用备份恢复该表,或按原定义再次
MODIFY回退。 - 若修改了全局
sql_mode,记录原值并改回;必要时重启会话或连接池使配置生效。 - 回退前先确认无正在进行的写入,避免数据不一致。
常见问题 #
- sql_mode 显示为空仍报错? 检查是否为会话级与全局级不一致,或客户端连接参数覆盖了设置。
- 只想加一个无关列,为何校验时间列? MySQL 在变更表结构时会重新解析整表定义,旧列的不合法默认值同样会被拦截。
- 能否直接清空 sql_mode? 会同时关闭严格模式等保护,可能掩盖数据截断等问题,不建议在生产使用。
- 多个时间列默认值限制? 早期版本对
CURRENT_TIMESTAMP默认值列数量有限制,请查阅对应版本文档确认。
修改
sql_mode属于实例级配置变更,影响面较大。生产环境请先备份、在测试环境验证,并保留回退方案。
