MySQL8.0版本以上支持递归查询 CTE

递归 CTE语法规则

WITH RECURSIVE cte_name[(col_name [, col_name] ...)] AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;

递归CTE由三个主要部分组成：

• 形成CTE结构的基本结果集的初始查询(initial_query)，初始查询部分被称为锚成员。
• 递归查询部分是引用CTE名称的查询，因此称为递归成员。递归成员由一个UNION、UNION ALL或者UNION DISTINCT运算符与锚成员相连。
• 终止条件是当递归成员没有返回任何行时，确保递归停止。

递归CTE的执行顺序如下：

1. 首先，将成员分为两个：锚点和递归成员。
2. 接下来，执行锚成员形成基本结果集(R0)，并使用该基本结果集进行下一次迭代。
3. 然后，将Ri结果集作为输入执行递归成员，并将Ri+1作为输出。
4. 之后，重复第三步，直到递归成员返回一个空结果集，换句话说，满足终止条件。
5. 最后，使用UNION ALL运算符将结果集从R0到Rn组合。

递归示例

递归输出1-10序列

WITH RECURSIVE seq_num(n) as (
	select 1
	UNION ALL
	select n+1 from seq_num where n < 10
)
select * from seq_num;

递归由下向上查询

WITH RECURSIVE organ_rec (organCode,parentCode)as (
	select organCode,parentCode from g_organ where organCode ='101.007.009.004.011'
	UNION ALL
	select g.organCode,g.parentCode from g_organ g INNER JOIN organ_rec  on  organ_rec.parentCode = g.organCode
)
select organCode,parentCode from organ_rec;

递归由上往下查询

WITH RECURSIVE organ_rec (organCode,parentCode)as (
	select organCode,parentCode from g_organ where parentCode ='-1'
	UNION ALL
	select g.organCode,g.parentCode from g_organ g INNER JOIN organ_rec  on  organ_rec.organCode = g.parentCode
)
select organCode,parentCode from organ_rec;

递归CTE的限制

递归CTE的查询语句中需要包含一个终止递归查询的条件。当由于某种原因在递归CTE的查询语句中未设置终止条件时，MySQL会根据相应的配置信息，自动终止查询并抛出相应的错误信息。在MySQL中默认提供了如下两个配置项来终止递归CTE。

• cte_max_recursion_depth：如果在定义递归CTE时没有设置递归终止条件，当达到cte_max_recursion_depth参数设置的执行次数后，MySQL会报错。
• max_execution_time：表示SQL语句执行的最长毫秒时间，当SQL语句的执行时间超过此参数设置的值时，MySQL报错。

查看cte_max_recursion_depth参数的默认值，并设置。

--- 默认1000
SHOW VARIABLES LIKE 'cte_max%';
--- 会话级别设置该值
SET SESSION cte_max_recursion_depth=999999999;

查看MySQL中max_execution_time参数的默认值，并设置。

--- 0:表示没有限制
SHOW VARIABLES LIKE 'max_execution%';
---单位为毫秒
SET SESSION max_execution_time=1000; 

总结

递归查询时，如果是由上往下查询时，建议通过类似层级码或者树型码字段，通过关键字 LIKE 查询。

参考：

1. https://www.yiibai.com/mysql/recursive-cte.html
2. 《MySQL技术大全》