UPDATE语句不支持ORDER BY，需用CTE+窗口函数（如ROW_NUMBER()）或变量子查询实现排序更新；MySQL 8.0+推荐CTE方案，5.7需依赖变量初始化与派生表；并发时须加事务和行锁避免覆盖。
UPDATE 语句中不能直接用 ORDER BY，必须借助子查询或窗口函数
SQL 标准里 UPDATE 本身不支持 ORDER BY，所以想按某字段排序后更新排名（比如按销售额从高到低设 rank_num），不能写成 UPDATE t SET rank_num = @r := @r + 1 ORDER BY sales DESC——这在 MySQL 8.0+ 会报错，在旧版可能偶然生效但行为不可靠。
真正可控的方式只有两种：用变量配合派生表（MySQL 5.7 及以前），或用窗口函数驱动的 CTE（MySQL 8.0+、PostgreSQL、SQL Server、Oracle 等）。
常见错误现象：ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY 或更新结果顺序混乱、排名重复/跳号。
- MySQL 5.7 及更早：必须把排序逻辑放在子查询里，再关联原表更新
- MySQL 8.0+ / PostgreSQL：优先用
ROW_NUMBER() OVER (ORDER BY ...)，语义清晰且可预测 - 别在
UPDATE中直接引用未定义的用户变量（如@r），变量初始化和执行顺序依赖执行计划，极易出错
MySQL 8.0+ 推荐写法：用 CTE + ROW_NUMBER()
这是目前最安全、最易读的方案。先用 CTE 算出目标排序和序号，再 JOIN 更新：
WITH ranked AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY score DESC, created_at ASC) AS new_rank FROM users ) UPDATE users u JOIN ranked r ON u.id = r.id SET u.rank_num = r.new_rank;
注意点：
-
ROW_NUMBER()保证严格连续、无重复（哪怕score相同，created_at也能打破并列） - 如果需要“相同分数同名次、跳过后续名次”（即
RANK()行为），就换用RANK() OVER (ORDER BY score DESC) - CTE 必须紧跟
UPDATE，不能加其他语句隔开；MySQL 要求 CTE 后的UPDATE显式指定别名（如u） - 若表数据量大，确保
ORDER BY字段有索引（如(score DESC, created_at ASC)），否则 CTE 内部排序很慢
MySQL 5.7 兼容写法：用变量子查询绕过 ORDER BY 限制
当无法升级时，只能用派生表模拟排序。核心是让变量在子查询中逐行计算，再与主表关联：
UPDATE users u JOIN ( SELECT id, @rn := @rn + 1 AS new_rank FROM users CROSS JOIN (SELECT @rn := 0) AS _ ORDER BY score DESC, created_at ASC ) r ON u.id = r.id SET u.rank_num = r.new_rank;
这个写法脆弱但有效，关键约束：
-
CROSS JOIN (SELECT @rn := 0)必须存在，且放在ORDER BY前——否则变量初始化时机不确定 -
ORDER BY必须出现在子查询内，不能移到外层 - MySQL 5.7 的执行引擎可能对 JOIN 顺序做优化，导致变量递增失效；测试时务必检查最大/最小
id对应的rank_num是否符合预期 - 该语句在开启
sql_mode=ONLY_FULL_GROUP_BY时可能报错，需确认变量用法被允许
UPDATE 排名时最容易被忽略的并发与事务问题
排名是典型“读-算-写”操作，多个会话同时执行会导致覆盖或错位。例如两个事务都读到当前最大 rank_num = 100，各自算出新排名从 101 开始，最终只有一组生效。
这不是语法问题，而是业务逻辑陷阱：
- 单次批量更新（如上面所有例子）建议包裹在事务中，并加
SELECT ... FOR UPDATE锁住相关行（尤其按条件筛选部分记录时） - 如果排名需实时维护（如用户每次提交成绩就重排），不要在应用层反复跑全表
UPDATE，考虑用触发器或物化视图替代 - 某些场景下，“排名”其实不该存为字段——比如分页查 Top 10，直接
SELECT ..., ROW_NUMBER() OVER (...) AS rank更轻量、更一致
真正难的从来不是怎么写那条 UPDATE，而是想清楚：这个排名是否必须落库？是否会被并发改写？有没有更无状态的表达方式？
