用于在中间行中插入新行的 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.
相关文章