在 MySql 中使用递归生成 ID 序列

Question

我们有一个表将transactions定义为；

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

如图所示，id不是连续的。目标是识别丢失的id。我正在尝试首先使用递归生成所有id。然后使用左联接来识别丢失的id。此处的预期输出应为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;

transactions表中的数字仅为15。但我得到了以下错误；

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。感谢您的帮助。

Answer 1

您的代码不应该引发该错误，如这个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 Fiddle演示