如何自动将新的id值设置为已删除行的id

Question

I have a table with 50 rows, id column as primary key, autoincrement.

At the beginning id values where from 1 - 50;

After deleting a row, for example id=1, and inserting a new row, this will have id=51.

How can I set the new row to take first empty place, it this case id=1 automatically, instead of 51;

Also if id=5 is deleted, the next inserted row should have id=5 an so on.

Answer 1

You can use MySQL's query variables to track the previous id and calculate the difference in the query, e,g.:

SELECT id, id - @previous AS difference, @previous := id
FROM test, (SELECT @previous := 0) a
ORDER BY id;

This would give you all the ids with difference. You can then wrap this into another query and SELECT the rows with difference > 1 to get the available id, e.g.:

SELECT (id - difference + 1) AS available_id
FROM (
 SELECT id, id - @previous AS difference, @previous := id
 FROM test, (SELECT @previous := 0) a
 ORDER BY id) b
WHERE b.difference > 1
LIMIT 1;

Here's the SQL Fiddle.