mysql auto increment filling the 'holes'

Question

Hi guys,

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

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

I have a table with more then 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'?

thx Paulo Bueno

Answer 1

There is a simple way but it doesn't perform well: Just try to insert with an id and when that fails, try the next one.

Alternatively, select an ID and when you don't get a result, use it.

If you're looking for a way to tell the DB to automatically fill the gaps, then that's not possible. Moreover, it should never be necessary. If you feel you need it, then you're abusing an internal technical key for something but the single purpose it has: To allow you to join tables.

[EDIT] If this is not a primary key, then you can use this update statement:

update (
    select *
    from table
    order by reg_id -- this makes sure that the order stays the same
)
set reg_id = x.nextval

where x is a new sequence which you must create. This will renumber all existing elements preserving the order. This will fail if you have foreign key constraints. And it will corrupt your database if you reference these IDs anywhere without foreign key constraints.

Note that during the next insert, the database will create a huge gap unless you reset the identity column.

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

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 5

You might wanna clean up gaps in a priority column. The way below will give an auto increment field for the priority. The extra left join on the same tabel will make sure it is added in the same order as (in this case) the priority

SET @a:=0;
REPLACE INTO footable
 (id,priority)
    (
    SELECT tbl2.id, @a 
    FROM footable as tbl
    LEFT JOIN footable as tbl2 ON tbl2.id = tbl.id  
    WHERE (select @a:=@a+1)
    ORDER BY tbl.priority
)