MySQL能否通过单条查询插入行并同步递增后续行的position字段值？

完全不需要通过PHP循环逐行逐次更新，我们可以通过批量原子操作高效完成指定位置挤入数据的需求，整体执行效果和你预期的表结构完全匹配，执行效率远高于循环更新。

关于你希望尽量用单条SQL完成操作的诉求：受限于MySQL的语法边界，强行把移位更新和插入逻辑合并为单条语句，很容易出现移位时把新插入的目标行本身也累加的逻辑漏洞，反而需要写复杂的子查询做排除，可维护性极差。用事务绑定批量更新和插入操作的方案，实际数据库交互开销极低，执行体验和单条语句的差异完全可以忽略，是生产环境验证过的最稳妥方案。

核心执行逻辑 #

操作分为两个不可分割的绑定步骤：一次性把所有position值大于等于插入目标位置的原有行，批量完成position值+1的偏移，之后再插入新行到目标位置即可。
把两个操作放到同一个InnoDB事务中执行，即可形成原子操作，避免并发下的位置错乱：

START TRANSACTION;
-- 批量后移目标位置及之后的所有原有行
UPDATE 你的表名
SET position = position + 1
WHERE position >= 3; -- 此处3为你指定的插入目标位置
-- 插入新行到指定位置
INSERT INTO 你的表名 (position, data)
VALUES (3, 'New York');
COMMIT;

执行完成后查询表数据，就会得到你预期的结构：

关键注意事项 #

• 一定要给position字段建立唯一索引，从数据库层面杜绝重复position值的异常，一旦出现排序值重复的问题可以立刻被索引校验拦截
• 上述批量更新的逻辑，不管需要后移多少行，都只需要单次数据库IO完成，相比PHP逐行循环N次UPDATE的方案，性能差距可达两个数量级以上，哪怕是几万行规模的排序表也能流畅执行
• 如果你希望尽量减少移位操作的开销，可以在初始化position值的时候预留固定间隔（比如初始值间隔100），插入新行的时候直接取两个相邻position值的中间数，无需全量移位；但这种方案在高频连续插入的场景下会出现间隔耗尽的问题，不适合频繁改动的排序表。

事务执行过程中，MySQL会对所有被UPDATE命中的行加排他锁，其他并发请求的同类移位操作会被阻塞直到当前事务提交，从机制上避免了移位错乱、position值重复的问题。

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