MySQL8.0-CTE递归查询（避免死循环）

 TSQL脚本能实现递归查询，用户使用共用表表达式 CTE(Common Table Expression)，只需要编写少量的代码，就能实现递归查询。

本文详细介绍CTE递归调用的特性和使用示例，递归查询主要用于层次结构的查询，从叶级(Leaf Level)向顶层(Root Level)查询，或从顶层向叶级查询，或递归的路径(Path)。

一、递归查询原理

递归调用是指自己调用自己，使用CTE实现递归查询必须满足三个条件：初始条件，递归调用表达式，终止条件。

CTE 递归查询的伪代码如下：

WITH  recursive  cte_name ( column_name [,...n]) AS (
CTE_query_definition
UNION ALL
CTE_query_definition
)
SELECT  *  FROM cte_name

1，递归查询至少包含两个子查询：

第一个子查询称作定点(Anchor)子查询：定点查询只是一个返回有效表的查询，用于设置递归的初始值；

第二个子查询称作递归子查询：该子查询调用CTE名称，触发递归查询，实际上是递归子查询调用递归子查询；

两个子查询使用union all，求并集；

2，CTE的递归终止条件

递归查询没有显式的递归终止条件，只有当递归子查询返回空结果集(没有数据行返回)或是超出了递归次数的最大限制时，才停止递归。

默认的递归查询次数是100，可以使用查询提示(hint)：MAXRECURSION 控制递归的最大次数：OPTION( MAXRECURSION 16)；如果允许无限制的递归次数，使用查询提示：option(maxrecursion 0)；当递归查询达到指定或默认的 MAXRECURSION 数量限制时，SQL Server将结束查询并返回错误，如下：

The statement terminated. The maximum recursion 10 has been exhausted before statement completion.

事务执行失败，该事务包含的所有操作都被回滚。在产品环境中，慎用maxrecursion 查询提示，推荐通过 where 条件限制递归的次数。

3，递归步骤

step1：定点子查询设置CTE的初始值，即CTE的初始值Set0；

递归调用的子查询过程：递归子查询调用递归子查询；

step2：递归子查询第一次调用CTE名称，CTE名称是指CTE的初始值Set0，第一次执行递归子查询之后，CTE名称是指结果集Set1；

step3：递归子查询第二次调用CTE名称，CTE名称是指Set1，第二次执行递归子查询之后，CTE名称是指结果集Set2；

step4：在第N次执行递归子查询时，CTE名称是指Set(N-1)，递归子查询都引用前一个递归子查询的结果集；

Step5：如果递归子查询返回空数据行，或超出递归次数的最大限制，停止递归；

二、递归查询演示

创建表：

CREATE TABLE `test_child_parent` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  `parent_name` varchar(255)  DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入数据：

INSERT INTO `test_child_parent`(`id`, `name`, `parent_id`, `parent_name`) VALUES (1, '一级', NULL, NULL);
INSERT INTO `test_child_parent`(`id`, `name`, `parent_id`, `parent_name`) VALUES (2, '二级', 1, '一级');
INSERT INTO `test_child_parent`(`id`, `name`, `parent_id`, `parent_name`) VALUES (3, '二级', 1, '一级');
INSERT INTO `test_child_parent`(`id`, `name`, `parent_id`, `parent_name`) VALUES (4, '三级', 2, '二级');
INSERT INTO `test_child_parent`(`id`, `name`, `parent_id`, `parent_name`) VALUES (5, '四级', 3, '二级');
INSERT INTO `test_child_parent`(`id`, `name`, `parent_id`, `parent_name`) VALUES (6, '五级', 5, '四级');
INSERT INTO `test_child_parent`(`id`, `name`, `parent_id`, `parent_name`) VALUES (7, '五级', 5, '四级');
INSERT INTO `test_child_parent`(`id`, `name`, `parent_id`, `parent_name`) VALUES (8, '五级', 5, '四级');

查询数据：

select  *  from  test_child_parent ;

向上递归查询

 with recursive temp as (
select * from  test_child_parent  p  where   id = 8
union all  
 select t.* from test_child_parent t inner join  temp t2  on  t2.parent_id = t.id  
)
select *  from temp;

查询结果：

 

 向下递归：

--查询下游节点（包含自己）
 with recursive temp as (
select * from  test_child_parent  p  where   id = 3
union all  
 select t.* from test_child_parent t inner join  temp t2  on  t2.id = t.parent_id
)
select *  from temp ; 

查询结果： 

 

三、特殊情况（死循环）

如果数据出现异常，比如 A -> B , B -> C , C -> A ，出现这样的情况，查询时就会出现死循环。

INSERT INTO `test_child_parent`(`id`, `name`, `parent_id`, `parent_name`) VALUES (3, '二级', 8, '五级');

这样再查询时就会报错：

Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

遇到这种死循环的递归查询，如何避免呢？

在MYSQL 8.109 引入了 LIMIT 语句,通过LIMIT 来限制输出数据的数量。

 with recursive temp as (
select * from  test_child_parent  p  where   id = 8
union all  
 select t.* from test_child_parent t inner join  temp t2  on  t2.parent_id = t.id   limit  10
)
select *  from temp ; 

查询结果：

 此时可以很快查询出结果，但是存在如下问题：

1、limit 返回行数，需要合理设置，否则返回的数据可以缺少；

2、查询的结果是重复的，需要进行去重处理，比如：在查询时加上distinct，或者使用group  by 等方式。

 with recursive temp as (
select * from  test_child_parent  p  where   id = 8
union all  
 select t.* from test_child_parent t inner join  temp t2  on  t2.parent_id = t.id  limit  1000 
)
select distinct *  from temp  ;