Question 113044 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to keep primary key column consecutive

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.

Tags:

2

Answers


  1. 0

    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.

    Trying to ensure consecutive id values is costly and unnecessary. You will always have gaps if you delete rows, or if you insert and then rollback. Also, InnoDB has some edge cases where it generates non-consecutive values (https://bugs.mysql.com/bug.php?id=57643).

    You expressed in a comment (now deleted) that you’re concerned that you could run out of values in your INT primary key.
    If your table has a high rate of inserts, and you think there’s a chance it could reach 232 id values, then use a BIGINT. Assuming you typically increment the id by 1, you could insert thousands of rows every second for centuries before you exhaust a BIGINT.

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search