Блог

У нас есть таблица, определенная transactions как;

 id
-----
 0
 1
 2
 5
 6
 8
 9
 10
 15
 

Как видно, идентификаторы не являются последовательными. Цель состоит в том, чтобы определить отсутствующие идентификаторы. Я пытаюсь сначала сгенерировать все идентификаторы с помощью рекурсии. А затем используйте left join для определения отсутствующих идентификаторов.
Здесь ожидаемый результат должен быть 3,4,7,11,12,13,14.

Вот мой подход;

     WITH RECURSIVE CTE (id) AS(
    
    SELECT MIN(id)
    FROM transactions
    UNION ALL
     SELECT id 1
     FROM CTE
     WHERE id<=(SELECT MAX(id) FROM transactions)
    
    
)

 SELECT *
FROM CTE;
 

Число доходит только до 15 в transactions таблице. Но я получаю следующую ошибку;

ERROR 3636 (HY000) at line 289: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value. Пожалуйста, обратите внимание, что версия MySQL 8.0.22. Помощь приветствуется.

Ваш код не должен вызывать эту ошибку, как показано в this db fiddle .

Однако вы могли бы немного оптимизировать запрос, избегая подзапроса в рекурсивном члене. Это было бы:

 with recursive cte as (
    select min(id) as id, max(id) as max_id from transactions 
    union all
    select id   1, max_id from cte where id < max_id
)
select c.id
from cte c
where not exists (select 1 from transactions t where t.id = c.id)
 

Однако это все еще в значительной степени метод грубой силы. Если у вас много строк и несколько пропущенных чисел, мы можем сделать запрос более эффективным, сгенерировав только диапазоны пропущенных чисел:

 with recursive 
    data as (
        select id, lead(id) over(order by id) lead_id from transactions
    ),
    cte as (
        select id   1 as id, lead_id - 1 as  max_id from data where lead_id > id   1  
        union all
        select id   1, max_id from cte where id < max_id
    )
select id from cte
 

Демонстрация на скрипке DB