语法:
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与服务端之间的通讯。