MySQL单语句交换两行layer字段值异常，求可靠实现方案

我有一个通过layer字段排序的tileplaceable表，UI中设有按钮可让条目上下移动，使选中行与相邻行交换layer值。我使用以下单条UPDATE语句实现：

UPDATE tileplaceable AS n1 JOIN tileplaceable AS n2 
ON (n1.p_id = ? AND n2.p_id = ? AND n1.t_id = ? AND n2.t_id = ?) 
OR (n2.p_id = ? AND n1.p_id = ? AND n1.t_id = ? AND n2.t_id = ?) 
SET n1.layer = n2.layer, n2.layer = @temp := n1.layer;

但偶尔UI会出现两行layer值相同的无效状态，我猜测该语句的执行顺序或原子性无法保证。请问我的猜测是否正确？或者有没有无需事务的可靠单语句实现方式？

你的猜测是对的，这条UPDATE语句的执行逻辑存在隐患。MySQL在执行多表关联的UPDATE时，不会严格按照SET子句的书写顺序执行赋值操作，而且依赖用户变量@temp的方式不具备原子性——当执行计划导致其中一行的layer先被覆盖后，另一行的赋值会拿到已经修改后的错误值，最终导致两行layer值相同。

如果要实现无需事务的可靠单语句交换，推荐两种可行方式：

方式一：算术运算交换值（适用于数值类型layer） #

利用数值计算直接完成值交换，不需要依赖用户变量，MySQL会提前读取所有字段的原始值，避免中途值被覆盖的问题：

UPDATE tileplaceable AS n1
JOIN tileplaceable AS n2
ON (n1.p_id = ? AND n2.p_id = ? AND n1.t_id = ? AND n2.t_id = ?)
OR (n2.p_id = ? AND n1.p_id = ? AND n1.t_id = ? AND n2.t_id = ?)
SET 
    n1.layer = n1.layer + n2.layer,
    n2.layer = n1.layer - n2.layer,
    n1.layer = n1.layer - n2.layer;

方式二：CASE语句精准赋值（通用型） #

如果layer不是数值类型，或者担心算术运算溢出，可以用CASE语句明确指定每行的目标值，通过唯一标识（p_id+t_id）确保赋值逻辑准确：

UPDATE tileplaceable AS n1
JOIN tileplaceable AS n2
ON (n1.p_id = ? AND n2.p_id = ? AND n1.t_id = ? AND n2.t_id = ?)
OR (n2.p_id = ? AND n1.p_id = ? AND n1.t_id = ? AND n2.t_id = ?)
SET 
    n1.layer = CASE WHEN n1.p_id = ? AND n1.t_id = ? THEN n2.layer ELSE n1.layer END,
    n2.layer = CASE WHEN n2.p_id = ? AND n2.t_id = ? THEN n1.layer ELSE n2.layer END;

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