MySql教程

【MySQL】递归查询

本文主要是介绍【MySQL】递归查询,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

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名称的查询,因此称为递归成员。递归成员由一个UNIONUNION ALL或者UNION DISTINCT运算符与锚成员相连。
  • 终止条件是当递归成员没有返回任何行时,确保递归停止。
递归CTE的执行顺序如下:
  1. 首先,将成员分为两个:锚点和递归成员。
  2. 接下来,执行锚成员形成基本结果集(R0),并使用该基本结果集进行下一次迭代。
  3. 然后,将Ri结果集作为输入执行递归成员,并将Ri+1作为输出。
  4. 之后,重复第三步,直到递归成员返回一个空结果集,换句话说,满足终止条件。
  5. 最后,使用UNION ALL运算符将结果集从R0Rn组合。

递归示例

递归输出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技术大全》
这篇关于【MySQL】递归查询的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!