MySQL添加列操作触发EndTime列默认值无效错误排查

这个问题其实和你添加新列的操作本身没有直接关系，根源在于MySQL 5.7默认启用的严格SQL模式在执行表结构变更时，对现有列的合法性进行了校验。

为什么会触发EndTime的错误？ #

MySQL 5.7默认开启了NO_ZERO_DATE和NO_ZERO_IN_DATE这两个SQL模式，这两个模式会禁止使用'0000-00-00 00:00:00'这类“零日期”作为默认值。虽然你只是添加新列，但执行ALTER TABLE语句时，MySQL会对整个表的结构进行完整性校验，发现EndTime列的默认值'0000-00-00 00:00:00'不符合当前的SQL模式规则，因此抛出了错误。

解决办法 #

这里提供几种可行的方案，你可以根据自己的需求选择：

1. 临时调整SQL模式完成操作

先查看当前的SQL模式配置：

SELECT @@sql_mode;

然后临时移除NO_ZERO_DATE和NO_ZERO_IN_DATE这两个规则：

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

接着执行你的添加列语句：

ALTER TABLE subscription ADD COLUMN trial TINYINT DEFAULT 0 NOT NULL;

如果之后需要恢复原来的严格模式，再把这两个规则加回去即可。

2. 永久修改SQL模式（需要重启MySQL）

找到MySQL的配置文件（通常是/etc/mysql/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf），修改sql_mode配置项，去掉NO_ZERO_DATE和NO_ZERO_IN_DATE：

sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

修改后重启MySQL服务：

sudo systemctl restart mysql

之后再执行添加列的操作就不会报错了。

3. 修复EndTime列的默认值（推荐长期方案）

从规范角度来说，最好直接修正EndTime列的默认值为合法日期，从根源上解决问题。比如可以改成NULL（如果业务允许）或者一个合法的初始日期：

ALTER TABLE subscription MODIFY COLUMN EndTime timestamp NOT NULL DEFAULT '1970-01-01 00:00:01';

注意执行这个操作前要确认现有数据的兼容性，避免影响业务。

内容的提问来源于stack exchange，提问作者DamiToma