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.

Answer 2

You have two major misunderstandings about how a relational database works:

1. there is no such thing as the "last row" in a relational database.
2. The ID (assuming that is your primary key) has no meaning whatsoever. It doesn't matter if the new row is assigned the 33, 35354 or 236532652632. It's just a value to uniquely identify that row.

Do not rely on consecutive values in your primary key column.

And do not try the max(id)+1 approach. It will simply not work in a system with more than one transaction.