我可以在表中插入("squeeze in")一行并在MySQL中通过一个查询增加插入行后的剩余值吗？

Question

我想知道我是否可以在表中插入一行并在插入行后增加所有剩余的值?

请看下面的示例表:

+----+----------+-----------+ 
| id | position | data      |
+----+----------+-----------+ 
| 1  |  1       | Paris     |
| 2  |  2       | London    |
| 3  |  3       | Berlin    |
| 4  |  4       | Madrid    |
+----+----------+-----------+ 

id列是主键。position列用于按自定义顺序对数据进行排序。我现在想知道是否有可能在MySQL中创建一个INSERT/UPDATE-query来"挤入"。表中的新城市，即插入后需要增加位置值，如下所示:

+----+----------+-----------+ 
| id | position | data      |
+----+----------+-----------+ 
| 1  |  1       | Paris     |
| 2  |  2       | London    |
| 5  |  3       | New York  |  <-- inserted row... positions after are increased
| 3  |  4       | Berlin    |  <-- position +1
| 4  |  5       | Madrid    |  <-- position +1
+----+----------+-----------+ 

我首先想到在PHP中通过修改循环中的每一行来解决这个问题。但是我希望我可以用一个insert查询来解决这个问题。这可能吗?

INSERT INTO table SET position=3, data="New York" {AND INCREASE THE REST of postion}

Answer 1

表中的行不是按照它们的主键值的顺序进行物理存储的(真正的答案，一如既往，是"这很复杂");如果你愿意，我很乐意详细说明这一点。

但是，别忘了你的问题，解决办法很简单:

SET @insertAtPosition = 3;
SET @insertData = 'New York';
BEGIN TRANSACTION;
UPDATE tbl SET Position = Position + 1 WHERE Position >= @insertAtPosition;
INSERT INTO tbl ( Position, Data ) VALUES ( @insertAtPosition, @insertData );
COMMIT TRANSACTION;

---

请注意，BEGIN TRANSACTION和COMMIT TRANSACTION的使用是重要的，这样Position的值不会在没有成功插入新行之前被更新-如果INSERT(或UPDATE)由于某种原因失败，那么Position的值将保持不变，就好像从未尝试过一样。