用于在中间行中插入新行的 MySQL 语法?
mysql sintax 用于在中间行或我们想要的任何地方插入新行而不更新现有行,但自动增加主键(id)?
mysql sintax for insert a new row in middle rows or wherever we want without updating the existing row, but automatically increment the primary key (id)?
' id | value
' 1 | 100
' 2 | 200
' 3 | 400
' 4 | 500
我想在 id 2 之后插入一个新行,值 = 300.我希望输出如下:
I want to insert a new row after id 2, with a value = 300. I want the output as below:
' id | value
' 1 | 100
' 2 | 200
' 3 | 300 <-- new row with id (automatic increment)
' 4 | 400 <-- id=id+1
' 5 | 500 <-- id=id+1
谢谢.
推荐答案
你必须把它分成 2 个操作.
You will have to split it into 2 operations.
START TRANSACTION;
UPDATE table1 SET id = id + 1 WHERE id >= 3 order by id DESC;
INSERT INTO table1 (id, value) VALUES (3, 300);
COMMIT;
请注意,更新语句中需要 order by
,因此它将首先从最高的 id 开始.
Notice that you need the order by
in the update statement, so it will start with the highest ids first.
另一个想法是将 id
声明为 decimal(10,1)
并在 2 和 3 之间插入值 2.5
作为 id.
Another idea would be to declare id
as decimal(10,1)
and insert value 2.5
as id in between 2 and 3.
相关文章