MySQL DATE_FORMAT处理无效日期报错，需将无效值置为NULL的问题

问题原因 #

MySQL开启严格SQL模式（如STRICT_TRANS_TABLES）时，即便单独执行DATE_FORMAT('test', '%W %M %Y')会返回NULL，但在UPDATE语句中，MySQL会优先校验输入值的日期合法性，发现'test'这类无效日期字符串后，会抛出[22001][1292]数据截断错误，而非直接将NULL赋值给字段。

解决方法 #

利用STR_TO_DATE()函数验证字符串是否为有效日期（转换失败返回NULL），再通过条件逻辑将无效日期对应的value字段设为NULL：

直接批量更新无效日期值为NULL

UPDATE extras
SET `value` = NULL
WHERE STR_TO_DATE(`value`, '%W %M %Y') IS NULL;

保留有效日期格式化结果，无效日期设为NULL

如果需要将合法日期按指定格式转换后保留，仅把无效日期设为NULL，可以用CASE WHEN实现：

UPDATE extras
SET `value` = CASE
    WHEN STR_TO_DATE(`value`, '%W %M %Y') IS NOT NULL
    THEN DATE_FORMAT(STR_TO_DATE(`value`, '%W %M %Y'), '%W %M %Y')
    ELSE NULL
END;

注意事项 #

不要为了规避报错关闭严格SQL模式，严格模式能有效拦截非法数据，保证数据的完整性和准确性。

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