用于在中间行中插入新行的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;

请注意，在update语句中需要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屋！