MySQL同WHERE条件SELECT正常但UPDATE执行报错的原因与解决方案

报错根本原因 #

• MySQL查询优化器对UPDATE语句的执行顺序没有强制保证先执行WHERE过滤再计算SET子句的表达式。当优化器判定提前计算表达式可以提升执行效率时，会先对全表所有行计算SET子句中的DATE_FORMAT(RIGHT(name, 10), "%Y-%m-%d")，第一行test nodate取右侧10位得到est nodate，无法转换为合法日期，在默认开启的严格SQL模式下会直接抛出错误，终止执行，不会进入后续的WHERE过滤步骤。
• 对照测试无报错是因为name <> name为恒假条件，优化器可以直接判定不存在需要更新的行，完全不会执行SET子句的表达式计算，因此不会触发格式错误。

可落地解决方案 #

方案1：添加IGNORE修饰符（最简便） #

给UPDATE语句增加IGNORE标识，会忽略执行过程中的非致命错误，仅更新符合条件且转换成功的行：

UPDATE IGNORE `test` SET
`order_date` = DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d")
WHERE DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") IS NOT NULL;

方案2：子查询先过滤再关联更新 #

通过派生表强制优化器先完成合法行筛选，再执行更新操作，避免对非法行计算转换表达式：

UPDATE `test` t1
INNER JOIN (
    SELECT `name` FROM `test`
    WHERE DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") IS NOT NULL
) t2 ON t1.`name` = t2.`name`
SET t1.`order_date` = DATE_FORMAT(RIGHT(t1.`name`, 10), "%Y-%m-%d");

方案3：用CASE表达式包裹转换逻辑 #

主动处理转换失败的场景，保证就算提前计算表达式也不会产生非法datetime值：

UPDATE `test` SET
`order_date` = CASE
    WHEN DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") IS NOT NULL
    THEN DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d")
    ELSE `order_date`
END
WHERE DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") IS NOT NULL;

方案4：临时关闭严格SQL模式（仅测试用，不推荐生产使用） #

执行更新前临时关闭严格日期检查，更新完成后恢复原有配置即可：

-- 临时关闭严格模式
SET sql_mode = '';
-- 执行UPDATE语句
UPDATE `test` SET
`order_date` = DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d")
WHERE DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") IS NOT NULL;

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