How to find "holes" in an auto_increment column?

Question

when I delete, for example, id 3, I have this:

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

Now, I want to find the missing identifiers, because I want to fill in the identifier again:

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

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

thanks!

Answer 1

You can find the upper value of the spaces, for example:

 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 

Answer 2

The purpose of AUTO_INCREMENT is to generate simple, unique and meaningless identifiers for your strings. As soon as you plan to reuse these identifiers, they are no longer unique (at least not in time), so I get 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 in SQL code, this query will match existing rows with rows that have the following identifier:

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

eg:.

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

Therefore, it is easy to filter the lines and get the first space:

 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.
• You need to lock the table to handle parallel inserts.
• On my computer, it is slow as hell with large tables.

Answer 3

I think the only way to do this is through a loop: Any other solutions will not show spaces larger 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 

Answer 4

Firstly, I agree with the comments that you should not try to fill the holes. You cannot find all holes with a single SQL statement. You will need to go through all possible numbers, starting from 1, until you find a hole. You can write a sql function to do this for you, which can then be used in a function. Therefore, if you wrote a function called find_first_hole, you can call it as an insert, for example:

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

Answer 5

This is a problem with spaces and islands, see my (and other) answers here and here . In most cases, problems with spaces and islands are most elegantly solved using recursive CTEs that are not available in mysql.