sql - mysql delete,autoincrement

Question

I have a table in MySQL using InnoDB and a column is there with the name "id".

So my problem is that whenever I delete the last row from the table and then insert a new value, the new value gets inserted after the deleted id.

I mean suppose my id is 32, and I want to delete it and then if I insert a new row after delete, then the column id auto-increments to 33. So the serial format is broken ie,id =30,31,33 and no 32.

So please help me out to assign the id 32 instead of 33 when ever I insert after deleting the last column.

Answer 1

Short answer: No.

Why?

1. It's unnecessary work. It doesn't matter, if there are gaps in the serial number.
2. If you don't want that, don't use auto_increment.
3. Don't worry, you won't run out of numbers if your column is of type int or even bigint, I promise.
4. There are reasons why MySQL doesn't automatically decrease the autoincrement value when you delete a row. Those reasons are
   • danger of broken data integrity (imagine multiple users perform deletes or inserts...doubled entries may occur or worse)
   • errors may occur when you use master slave replication or transactions
   • and so on ...

I highly recommend you don't waste time on this! It's really, really error prone.