MySQL 5.7中UPDATE语句子查询与主查询排序不一致问题

这个问题我之前升级MySQL版本时也踩过坑！MySQL 5.7对查询优化器做了不少底层调整，其中**派生表合并（derived_merge）**这个默认开启的优化项，就是导致你原来的语句出问题的元凶。

问题根源 #

在MySQL 5.6及更早版本里，你写的那个JOIN子查询（用来生成行号的派生表）会严格按照你指定的顺序执行：先排序，再逐行给@row变量赋值生成新的sequenceNumber。但5.7默认会把派生表和主查询合并执行，打乱了“先排序再赋值变量”的顺序，导致生成的行号和你期望的排序对不上，最终UPDATE后的sequenceNumber就乱了。

具体解决方案 #

给你两个靠谱的解决办法，优先用第一个，对业务影响最小：

1. 给子查询加优化提示，禁止派生表合并

在子查询的SELECT语句里加上/*+ NO_MERGE(表别名) */的优化提示，强制优化器单独执行这个子查询，保证排序和变量赋值的顺序。

修改后的完整SQL示例（假设你要按原sequenceNumber排序重新编号，或者替换成你需要的排序规则）：

-- 先初始化变量，避免脏数据
SET @row := 0;

UPDATE imageFile f1 
JOIN (
    -- 加NO_MERGE提示，强制子查询先执行排序再生成行号
    SELECT /*+ NO_MERGE(f2) */ f2.id, (@row := @row + 1) AS newSequenceNumber
    FROM imageFile f2
    WHERE f2.itemId = 123 -- 替换成实际的itemId值
    ORDER BY f2.sequenceNumber -- 这里是你需要的排序依据，比如用户自定义顺序可以用FIELD(f2.id, 4,2,5...)
) AS f3 ON f1.id = f3.id
SET f1.sequenceNumber = f3.newSequenceNumber, 
    f1.updatedDate = CURRENT_DATE();

2. 会话级关闭derived_merge优化（不推荐全局关闭）

如果不想给每个语句加提示，可以临时关闭当前会话的派生表合并优化：

SET SESSION optimizer_switch = 'derived_merge=off';

执行完你的UPDATE语句后，再开回去：

SET SESSION optimizer_switch = 'derived_merge=on';

不过这个方法会影响当前会话的其他查询，所以只适合临时调试，不建议在生产环境长期用。

额外提醒 #

如果你的排序是用户自定义的（比如用户拖动图片调整顺序后提交的ID列表），记得把子查询里的ORDER BY改成FIELD(f2.id, 要排的ID1, ID2, ID3...)，这样能严格按照用户指定的顺序生成行号。

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