递归sql，用于实现存储树状结构的表进行查询

语法：

 WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

cte_name :公共表达式的名称,可以理解为表名,用来表示as后面跟着的子查询

col_name :公共表达式包含的列名,可以写也可以不写

例子：

with RECURSIVE t1  AS
(
  SELECT 1 as n
  UNION ALL
  SELECT n + 1 FROM t1 WHERE n < 5
)
SELECT * FROM t1;

输出：

 

说明：

t1 相当于一个表名

select 1 相当于这个表的初始值，这里使用UNION ALL 不断将每次递归得到的数据加入到表中。

n<5为递归执行的条件，当n>=5时结束递归调用。

 

表结构：

 

 使用递归实现课程分类的查询

向上递归 

with recursive t1 as (
select * from  course_category p where  id= '1'
union all
 select t.* from course_category t inner join t1 on t1.id = t.parentid
)
select *  from t1 order by t1.id, t1.orderby

t1表中初始的数据是id等于1的记录，即根结点。

通过inner join t1 t2 on t2.id = t.parentid 找到id='1'的下级节点 。

通过这种方法就找到了id='1'的所有下级节点，下级节点包括了所有层级的节点。

 

向下递归

 

with recursive t1 as (
select * from  course_category p where  id= '1-1-1'
union all
 select t.* from course_category t inner join t1 on t1.parentid = t.id
)
select *  from t1 order by t1.id, t1.orderby

 

初始节点为1-1-1，通过递归找到它的父级节点，父级节点包括所有级别的节点。

以上是我们研究了树型表的查询方法，通过递归的方式查询课程分类比较灵活，因为它可以不限制层级。

mysql为了避免无限递归默认递归次数为1000，可以通过设置cte_max_recursion_depth参数增加递归深度，还可以通过max_execution_time限制执行时间，超过此时间也会终止递归操作。

mysql递归相当于在存储过程中执行若干次sql语句，java程序仅与数据库建立一次链接执行递归操作，所以只要控制好递归深度，控制好数据量性能就没有问题。

 作用：

根据自己的业务需求来进行设计sql，能查询到简洁便于操作的数据和有效的减少sql与服务端之间的通讯。