MySQL新增索引报Invalid default value 无法修改列默认值如何解决

问题原因 #

这个报错是因为你的MySQL实例开启了NO_ZERO_DATE的SQL模式，该模式禁止日期类型使用0000-00-00作为合法值。执行ALTER TABLE操作时，MySQL会重新校验所有表列的定义合法性，所以即使你修改的是其中一列，只要其他列存在不符合规则的默认值，依旧会触发报错。

解决步骤 #

1. 临时修改当前会话的SQL模式，移除零日期限制，保证后续ALTER操作可以正常执行：

SET SESSION sql_mode = REPLACE(@@sql_mode, 'NO_ZERO_DATE', '');
SET SESSION sql_mode = REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', '');

2. 先清理表内已经存在的无效零值日期，避免后续操作再次触发校验问题：

UPDATE listings SET completion_date = NULL WHERE completion_date = '0000-00-00';
UPDATE listings SET expired = NULL WHERE expired = '0000-00-00';

3. 批量修改两个日期列的定义，将默认值改为NULL：

ALTER TABLE `listings` 
CHANGE `completion_date` `completion_date` DATE NULL DEFAULT NULL,
CHANGE `expired` `expired` DATE NULL DEFAULT NULL;

4. 最后执行添加索引的语句即可：

ALTER TABLE listings ADD INDEX added_date (added_date);

补充说明 #

如果需要永久避免这类问题，可以修改MySQL配置文件（my.cnf/my.ini）中的sql_mode参数，移除NO_ZERO_DATE相关配置后重启实例即可。如果业务本身不需要零日期格式，建议保留该模式，只在改表时临时关闭即可。

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