MySQL单条UPDATE语句更新排序字段实现记录顺序调整

• 假设MySQL中表test_group初始数据如下：

mysql> select * from test_group;

+----+------------+---------------+
| id | group_name | display_order |
+----+------------+---------------+
|  1 | A          |             1 |
|  2 | B          |             2 |
|  3 | C          |             3 |
|  4 | D          |             4 |
|  5 | E          |             5 |
+----+------------+---------------+

• 需求为将原display_order=5的记录移动到display_order=3的记录之前，调整后期望数据如下：

mysql> select * from test_group;

+----+------------+---------------+
| id | group_name | display_order |
+----+------------+---------------+
|  1 | A          |             1 |
|  2 | B          |             2 |
|  3 | C          |             4 |
|  4 | D          |             5 |
|  5 | E          |             3 |
+----+------------+---------------+

• 排序规则变化：
  • 原始排序：A(1) → B(2) → C(3) → D(4) → E(5)
  • 调整后排序：A(1) → B(2) → E(3) → C(4) → D(5)

原有SQL可正常执行，但存在冗余判断，写法不够简洁：

update
    test_group
set
    display_order =(case
        when display_order >= 3
        and display_order <5 then display_order + 1
        when id = 5 then 3
        else display_order
    end)
where
    display_order in (3,4,5);

针对本次需求的简化写法 #

由于WHERE子句已经限定了只会更新display_order为3、4、5的三行记录，不需要在CASE中写多余的范围判断和ELSE分支（不在更新范围内的行不会被修改），可以直接简化为：

UPDATE test_group
SET display_order = CASE
    WHEN id = 5 THEN 3
    ELSE display_order + 1
END
WHERE display_order BETWEEN 3 AND 5;

这个写法逻辑更直白：只处理排序值在3到5之间的记录，其中id=5的记录直接设为目标排序3，剩下两条原本排序为3、4的记录统一+1后移一位，刚好匹配预期结果，没有冗余判断。

通用拖拽排序场景扩展 #

如果要适配任意位置的排序移动（把原排序为@old_pos的记录移动到@new_pos位置），可以把位置参数抽离，分场景写逻辑：

• 向前移动（@new_pos < @old_pos，即本次需求的场景）：

-- 本次需求代入 @old_pos=5, @new_pos=3
UPDATE test_group
SET display_order = CASE
    WHEN display_order = @old_pos THEN @new_pos
    ELSE display_order + 1
END
WHERE display_order BETWEEN @new_pos AND @old_pos;

• 向后移动（@new_pos > @old_pos）：

UPDATE test_group
SET display_order = CASE
    WHEN display_order = @old_pos THEN @new_pos
    ELSE display_order - 1
END
WHERE display_order BETWEEN @old_pos AND @new_pos;

注意：执行排序更新前建议先开启事务，确认更新结果符合预期后再提交，避免排序错乱。

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