how find "holes" in auto_increment column?

Question

when I DELETE, as example, the id 3, I have this:

id | name
 1 |
 2 |    
 4 |
 5 |
 ...

now, I want to search for the missing id(s), because i want to fill the id again with:

INSERT INTO xx (id,...) VALUES (3,...)

is there a way to search for "holes" in the auto_increment index?

thanks!

Clapper

Why do you want to do this? Such "holes" are normal within day to day database operation and shouldn't be a concern. If this is due to some misguided notion of aesthetics, resist it. – Lavish

You should not. An auto_increment should just do its job, and that means holes can be made. If you start messing with that, you'll run in to trouble later on. Just let them be. – Verniavernice

Not to mention that if a deleted ID is still referenced somewhere i.e. through a bookmarked link / un-updated table, then adding a new row in with that ID will produce unintended problems. As stated by Oded, Nanne, just leave the AI to do its job. – Alexiaalexin

As others have said: You shouldn't care and you don't need to do it. – Nonagon

Antijoin to an auxilliary 'Numbers table' of unique integer values. – Fay

@a_horse_with_no_name: the problem is, even if one agree that surrogate key values should not be exposed to users, one never supposes the user in question is oneself! – Fay

deleted ids are not referenced in no way at all... so the id is free and i can reuse it safely. – Clapper

ok: this is not a good idea... but thanks for the infos! you tell me what is the best answer? xD – Clapper

Answer 1

You can find the top value of gaps like this:

select t1.id - 1 as missing_id
from mytable t1
left join mytable t2 on t2.id = t1.id - 1
where t2.id is null

Choli

what if you have a gab bigger than 1? if you have 14 and then 20, it will show only 19 – Habiliment

@Habiliment Note that my answer says my query will find the top value of gaps. You figure out what will happen if the gap is larger than 1 – Choli

Answer 2

The purpose of AUTO_INCREMENT is to generate simple unique and meaningless identifiers for your rows. As soon as you plan to re-use those IDs, they're no longer unique (not at least in time) so I have the impression that you are not using the right tool for the job. If you decide to get rid of AUTO_INCREMENT, you can do all your inserts with the same algorithm.

As about the SQL code, this query will match existing rows with the rows that has the next ID:

SELECT a.foo_id, b.foo_id
FROM foo a
LEFT JOIN foo b ON a.foo_id=b.foo_id-1

E.g.:

 1 NULL
 4 NULL
10 NULL
12 NULL
17 NULL
19   20
20 NULL
24   25
25   26
26   27
27 NULL

So it's easy to filter out rows and get the first gap:

SELECT MIN(a.foo_id)+1 AS next_id
FROM foo a
LEFT JOIN foo b ON a.foo_id=b.foo_id-1
WHERE b.foo_id IS NULL

Take this as a starting point because it still needs some tweaking:

• You need to consider the case where the lowest available number is the lowest possible one.
• You need to lock the table to handle concurrent inserts.
• In my computer it's slow as hell with big tables.

Ayannaaycock

thank you for additional infos (In my computer it's slow as hell with big tables.) – Clapper

Answer 3

I think the only way you can do this is with a loop: Any other solutions wont show gaps bigger than 1:

insert into XX values (1)
insert into XX values (2)
insert into XX values (4)
insert into XX values (5)
insert into XX values (10)

declare @min int
declare @max int

select @min=MIN(ID) from xx
select @max=MAX(ID) from xx

while @min<@max begin
    if not exists(select 1 from XX where id = @min+1) BEGIN
        print 'GAP: '+ cast(@min +1 as varchar(10))
    END

    set @min=@min+1
end

result:

GAP: 3
GAP: 6
GAP: 7
GAP: 8
GAP: 9

Habiliment

Answer 4

First, I agree with the comments that you shouldn't try filling in holes. You won't be able to find all the holes with a single SQL statement. You'll have to loop through all possible numbers starting with 1 until you find a hole. You could write a sql function to do this for you that could then be used in a function. So if you wrote a function called find_first_hole you could then call it in an insert like:

INSERT INTO xx (id, ...) VALUES (find_first_hole(), ...)

Worktable

Answer 5

This is a gaps&island problem, see my (and other) replies here and here. In most cases, gaps&islands problems are most elegantly solved using recursive CTE's, which are not available in mysql.

Interrogation