MySQL syntax for inserting a new row in middle row

Question

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

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 

Thanks.

Answer 1

You can't use the autoincrement feature for the new row. The next autoincrement value will always be after the last autoincrement value generated; never an intermediate value. The only way I see to do this is rather painful: insert a new row that is a copy of the last row (to bump the autoincrement number for the table) and then update all the remaining rows (including the one you think of as the new one).

If the values are unique, I wonder about the wisdom of using a separate id column. Why not make the value column the primary key?

Answer 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;

Notice that you need the order by in the update statement, so it will start with the highest ids first.

Another idea would be to declare id as decimal(10,1) and insert value 2.5 as id in between 2 and 3.

Answer 3

insert into my_table (value)
values (select value+100 from my_table order by id desc limit 1)