I have a table with integer ID as primary key--auto increment. Because of frequent delete/insert, the ID values are like 1 1000 2340 5420 ....

I want it always to be 1 2 3 4 ....

So is there a way--while inserting a new row--to insert a first missing ID value automatically--instead of constantly increment values?

Example: If current values are 1 3 4 5, the next insert should be 1 2 3 4 5 and not 1 3 4 5 6.

CodePudding user response：

No. Auto-increment needs to work with maximum throughput, to support concurrent sessions inserting to the same table. It can't do that and also find gaps. That would lead to race conditions.

To prevent race conditions, it would be required for a session to lock the table, which would block concurrent access to the table.

You should not need the id to have sequential values. They are required to be unique, but not consecutive. They are not row numbers.

You should read https://dev.mysql.com/doc/refman/en/innodb-auto-increment-handling.html to get more details about how the auto-increment works in MySQL.

I also wrote more about this in a chapter of my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

CodePudding user response：

This answer might help you, the idea is to create a table to hold sequence value and increase it with LAST_INSERT_ID() feature.