php - how to automatically set new id value as id of already deleted row -


i have table 50 rows, id column primary key, autoincrement.

at beginning id values 1 - 50;

after deleting row, example id=1, , inserting new row, have id=51.

how can set new row take first empty place, case id=1 automatically, instead of 51;

also if id=5 deleted, next inserted row should have id=5 on.

you can use mysql's query variables track previous id , calculate difference in query, e,g.:

select id, id - @previous difference, @previous := id test, (select @previous := 0) order id; 

this give ids difference. can wrap query , select rows difference > 1 available id, e.g.:

select (id - difference + 1) available_id (  select id, id - @previous difference, @previous := id  test, (select @previous := 0)  order id) b b.difference > 1 limit 1; 

here's sql fiddle.


Comments

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

vue.js - Create hooks for automated testing -

Add new key value to json node in java -