How to fill in the "holes" in auto-increment fields?

Question

I've read some posts about this but none cover this issue.

I guess its not possible, but I'll ask anyway.

I have a table with more than 50.000 registers. It's an old table where various insert/delete operations have taken place.

That said, there are various 'holes' some of about 300 registers. I.e.: ..., 1340, 1341, 1660, 1661, 1662,...

The question is. Is there a simple/easy way to make new inserts fill these 'holes'?

Answer 1

I agree with @Aaron Digulla and @Shane N. The gaps are meaningless. If they DO mean something, that is a flawed database design. Period.

That being said, if you absolutely NEED to fill these holes, AND you are running at least MySQL 3.23, you can utilize a TEMPORARY TABLE to create a new set of IDs. The idea here being that you are going to select all of your current IDs, in order, into a temporary table as such:

CREATE TEMPORARY TABLE NewIDs
(
    NewID INT UNSIGNED AUTO INCREMENT,
    OldID INT UNSIGNED
)

INSERT INTO NewIDs (OldId)
SELECT
    Id
FROM
    OldTable
ORDER BY
    Id ASC

This will give you a table mapping your old Id to a brand new Id that is going to be sequential in nature, due to the AUTO INCREMENT property of the NewId column.

Once this is done, you need to update any other reference to the Id in "OldTable" and any foreign key it utilizes. To do this, you will probably need to DROP any foreign key constraints you have, update any reference in tables from the OldId to the NewId, and then re-institute your foreign key constraints.

However, I would argue that you should not do ANY of this, and just understand that your Id field exists for the sole purpose of referencing a record, and should NOT have any specific relevance.

UPDATE: Adding an example of updating the Ids

For example:

Let's say you have the following 2 table schemas:

CREATE TABLE Parent
(
    ParentId INT UNSIGNED AUTO INCREMENT,
    Value INT UNSIGNED,
    PRIMARY KEY (ParentId)
)

CREATE TABLE Child
(
    ChildId INT UNSIGNED AUTO INCREMENT,
    ParentId INT UNSIGNED,
    PRIMARY KEY(ChildId),
    FOREIGN KEY(ParentId) REFERENCES Parent(ParentId)
)

Now, the gaps are appearing in your Parent table.

In order to update your values in Parent and Child, you first create a temporary table with the mappings:

CREATE TEMPORARY TABLE NewIDs
(
    Id INT UNSIGNED AUTO INCREMENT,
    ParentID INT UNSIGNED
)

INSERT INTO NewIDs (ParentId)
SELECT
    ParentId
FROM
    Parent
ORDER BY
    ParentId ASC

Next, we need to tell MySQL to ignore the foreign key constraint so we can correctly UPDATE our values. We will use this syntax:

SET foreign_key_checks = 0;

This causes MySQL to ignore foreign key checks when updating the values, but it will still enforce the correct value type is used (see MySQL reference for details).

Next, we need to update our Parent and Child tables with the new values. We will use the following UPDATE statement for this:

UPDATE
    Parent,
    Child,
    NewIds
SET
    Parent.ParentId = NewIds.Id,
    Child.ParentId = NewIds.Id
WHERE
    Parent.ParentId = NewIds.ParentId AND
    Child.ParentId = NewIds.ParentId

We now have updated all of our ParentId values correctly to the new, ordered Ids from our temporary table. Once this is complete, we can re-institute our foreign key checks to maintain referential integrity:

SET foreign_key_checks = 1;

Finally, we will drop our temporary table to clean up resources:

DROP TABLE NewIds

And that is that.

Answer 2

What is the reason you need this functionality? Your db should be fine with the gaps, and if you're approaching the max size of your key, just make it unsigned or change the field type.

Answer 3

You generally don't need to care about gaps. If you're getting to the end of the datatype for the ID it should be relatively easy to ALTER the table to upgrade to the next biggest int type.

If you absolutely must start filling gaps, here's a query to return the lowest available ID (hopefully not too slowly):

SELECT MIN(table0.id)+1 AS newid
FROM table AS table0
LEFT JOIN table AS table1 ON table1.id=table0.id+1
WHERE table1.id IS NULL

(remember to use a transaction and/or catch duplicate key inserts if you need concurrent inserts to work.)

Answer 4

INSERT INTO prueba(id) 
VALUES (
(SELECT IFNULL( MAX( id ) , 0 )+1 FROM prueba target))

IFNULL for skip null on zero rows count

add target for skip error mysql "error clause FROM)