как найти дыры в столбце auto_increment?

Вы можете найти максимальное значение пробелов следующим образом:

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

Целью AUTO_INCREMENT является создание простых уникальных и бессмысленных идентификаторов для ваших строк. Как только вы планируете повторно использовать эти идентификаторы, они перестают быть уникальными (по крайней мере, со временем), поэтому у меня сложилось впечатление, что вы используете не тот инструмент для работы. Если вы решили избавиться от AUTO_INCREMENT, вы можете делать все свои вставки по тому же алгоритму.

Что касается кода SQL, этот запрос сопоставит существующие строки со строками со следующим идентификатором:

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

Так что легко отфильтровать строки и получить первый пробел:

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

Возьмите это как отправную точку, потому что она все еще нуждается в некоторой настройке:

• Вам необходимо рассмотреть случай, когда наименьшее доступное число является наименьшим возможным.
• Вам нужно заблокировать таблицу для обработки одновременных вставок.
• На моем компьютере это чертовски медленно с большими таблицами.

Я думаю, что единственный способ сделать это - с помощью цикла: любые другие решения не будут отображать пробелы больше 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

результат:

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

Во-первых, я согласен с комментариями о том, что не стоит пытаться затыкать дыры. Вы не сможете найти все дыры с помощью одного оператора SQL. Вам придется перебирать все возможные числа, начиная с 1, пока не найдете дыру. Вы можете написать функцию sql, чтобы сделать это для вас, которую затем можно было бы использовать в функции. Итак, если вы написали функцию с именем find_first_hole, вы могли бы вызвать ее во вставке, например:

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

Это проблема пробелов и островов, см. мои (и другие) ответы здесь и здесь. В большинстве случаев проблемы с пробелами и островами наиболее элегантно решаются с помощью рекурсивных CTE, которые недоступны в mysql.