用于在中间行中插入新行的 MySQL 语法? [英] MySQL syntax for inserting a new row in middle rows?

问题描述

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.

这篇关于用于在中间行中插入新行的 MySQL 语法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！