是否存在最优方案通过单次请求交换数据库表position字段值？

核心思路是通过单次UPDATE语句完成交换，不需要发起多次数据库请求，主流数据库均支持以下实现方式：

已知两条记录ID和对应要交换的position值 #

直接通过CASE表达式匹配ID赋值，所有关系型数据库通用：

UPDATE 表名
SET position = CASE
    WHEN id = 待交换记录1的ID THEN 待交换记录2的原position值
    WHEN id = 待交换记录2的ID THEN 待交换记录1的原position值
END
WHERE id IN (待交换记录1的ID, 待交换记录2的ID);

举个实际示例：交换ID为2、position为1，和ID为5、position为3的两条记录：

UPDATE your_table
SET position = CASE
    WHEN id = 2 THEN 3
    WHEN id = 5 THEN 1
END
WHERE id IN (2,5);

无需提前查询position值的动态写法 #

如果不想预先查询两条记录的position值，可以用自连接的方式在单次更新内完成值的交换：

MySQL 写法 #

UPDATE your_table t1
JOIN your_table t2 ON t1.id = 2 AND t2.id = 5
SET t1.position = t2.position,
    t2.position = t1.position;

PostgreSQL 写法 #

UPDATE your_table t1
SET position = t2.position
FROM your_table t2
WHERE (t1.id, t2.id) IN ((2,5), (5,2));

注意事项 #

如果position字段设置了唯一约束也无需担心，数据库的更新操作是原子性的，唯一约束会在整批更新完成后才做校验，不会触发中间状态的冲突报错。

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