MySQL 5.7更新日期格式报错：str_to_date空值问题如何解决？

问题根源 #

你的column1列中不仅存在NULL值，还包含空字符串''。STR_TO_DATE函数处理空字符串时会抛出Incorrect datetime value错误，查询时可能仅扫描了有效数据行未触发问题，而全表更新时则会暴露该错误。

解决方案 #

方案1：在更新语句中直接处理空值 #

使用NULLIF将空字符串转换为NULL，再传递给日期处理函数，避免报错。执行以下更新语句：

UPDATE table 
SET `column1` = DATE_FORMAT(STR_TO_DATE(NULLIF(`column1`, ''), '%Y-%m-%d %H:%i:%s'), '%m/%d/%Y');

• 逻辑说明：NULLIF(column1, '')会把空字符串替换为NULL，STR_TO_DATE和DATE_FORMAT对NULL值均返回NULL，既保留了原有的空值，又能正常转换有效日期字符串。

如果需要更明确的条件判断，也可以用CASE语句：

UPDATE table 
SET `column1` = CASE 
                  WHEN `column1` IS NOT NULL AND `column1` != '' 
                  THEN DATE_FORMAT(STR_TO_DATE(`column1`, '%Y-%m-%d %H:%i:%s'), '%m/%d/%Y') 
                  ELSE NULL 
                END;

方案2：先统一清理空字符串为NULL #

如果希望彻底统一空值格式，可以先将所有空字符串转为NULL，再执行原更新语句：

1. 清理空字符串：

UPDATE table SET `column1` = NULL WHERE `column1` = '';

2. 执行原更新语句：

UPDATE table set `column1` = date_format(str_to_date(`column1`, '%Y-%m-%d %H:%i:%s'), '%m/%d/%Y');

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