MySQL中直接交换两行order_num不能用子查询UPDATE，因违反ERROR 1093限制；推荐用用户变量或临时表实现，避免中间值污染和并发问题。
MySQL中直接交换两行位置为什么不能用UPDATE SET ... = ...
因为MySQL不允许在同一个UPDATE语句中，对同一张表既做查询又做更新（会报错 ERROR 1093 (HY000): You can't specify target table 't' for update in FROM clause）。哪怕你写成 UPDATE t SET order_num = (SELECT order_num FROM t WHERE id = 2) WHERE id = 1，也会触发这个限制。临时表或变量是绕过它的必要手段。
用用户变量实现两行order_num互换（推荐）
适用于有排序字段（如 order_num）的场景，且两行ID已知。核心是避免子查询引用原表，改用变量暂存值：
SET @a := 0, @b := 0; SELECT @a := order_num FROM t WHERE id = 1; SELECT @b := order_num FROM t WHERE id = 2; UPDATE t SET order_num = CASE WHEN id = 1 THEN @b WHEN id = 2 THEN @a ELSE order_num END WHERE id IN (1, 2);
注意三点：
- 必须用两个独立
SELECT赋值，不能合并成一条（否则变量赋值顺序不可控） -
CASE中要加ELSE order_num，否则其他行会被设为NULL - 该方法在开启
sql_safe_updates=1时仍可执行，因WHERE明确限定了主键条件
用临时表避免变量依赖（适合批量或复杂条件）
当需要交换多对行、或条件动态生成时，变量容易出错。此时建临时表存映射关系更可靠：
CREATE TEMPORARY TABLE swap_map (id INT PRIMARY KEY, new_order INT); INSERT INTO swap_map VALUES (1, 200), (2, 100); -- 假设要把id=1的order_num改成200，id=2改成100 UPDATE t JOIN swap_map USING (id) SET t.order_num = swap_map.new_order;
优势明显：
- 一次可处理任意数量行，无需为每对写单独逻辑
- 不依赖会话变量，跨连接/重连安全
- 可结合
SELECT ... INTO TEMPORARY TABLE动态生成映射，比如按当前最大值反序重排
三次UPDATE法的陷阱与适用边界
所谓“三次更新”指：用一个中间值暂存，例如 UPDATE t SET order_num = -1 WHERE id = 1 → UPDATE t SET order_num = (SELECT order_num FROM t WHERE id = 2) WHERE id = 1 → UPDATE t SET order_num = -1 WHERE id = 2。这看似简单，但风险极高：
- 中间值若与业务值冲突（比如
order_num允许负数），会导致数据污染 - 并发下可能被其他事务读到中间状态（-1），破坏一致性
- 没加事务包裹时，第二步失败会导致数据卡在脏状态
除非你能保证中间值绝对唯一（如用超大随机数+时间戳拼接），且操作在事务内完成，否则别碰这种方法。
真正容易被忽略的是：交换本质不是“改两行”，而是“维护全局顺序约束”。如果业务要求 order_num 必须连续无重复，交换后还得检查是否产生间隙或重复——那就要额外加校验逻辑，而不是只盯着交换本身。
