树型表查询

树型表查询

在开发中，很常见的会用到树形结构，以课程分类结构，其在数据库中的表示如下：

通常都是通过 parentId 来描述父亲节点。

查询方式有两种：固定层级查询、递归查询

1、固定层级查询

如果树的层级固定可以使用表的自链接去查询，比如：我们只查询两级课程分类，可以用下边的SQL

SELECT
	a.id 		a_id,
	a.`name` 	a_name,
	b.id 		b_id,
	b.`name` 	b_name,
	c.id 		c_id,
	c.`name` 	c_name
FROM
	course_category a
	INNER JOIN course_category b ON b.parentid = a.id
	INNER JOIN course_category c ON c.parentid = b.id
WHERE
	a.parentid = 0
	AND a.is_show = 1
	AND b.is_show = 1
ORDER BY
	a.orderby,
	b.orderby

存在几级结构就添加几个 inner join 语句。

内连接查询就是通过parentid找父节点

1.一次内连接

2.两次内连接

可以看到，只有在树的层级确定的情况下我才能选择性的去自连接子表，某种意义上来讲这种方法存在弊端，我要是insert进去层级更低的新子节点那我的sql就得改变，从而就造成了一个“动一发而牵全身”的硬编码问题，实在是不够稳妥！

所以该方法只在层级较低查询时简单使用。

2、递归查询

注意：MySQL8.0版本开始支持递归查询，
MySQL递归实现，使用with语法，如下：

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

1.向下递归：从根节点开始找子节点

先来看一个简单的Demo

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

具体理解如下：

with RECURSIVE t as
1.定义一个递归表 t,所有的操作都是基于该表
select 1 as count
2.初始化递归表 t
union all
3.将步骤4中的操作结果合并到递归表中
select count + 1 from t where n < 5
4.每次从递归表 t中取出n，对n进行+1操作
select * from t;
5.查询递归表数据

上接内连接查询的案例，使用递归方式查询课程分类

with RECURSIVE t as (
select * from course_category as cc where  cc.id = '1'
union all
select c.* from course_category c INNER JOIN t on t.id = c.parentid
)
select * from t order by t.id,t.orderby;

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

通过 c inner join t on t.id = c.parentid 找到id='1'的下级节点 。
通过这种方法就找到了id='1'的所有下级节点，下级节点包括了所有层级的节点。
上边这种方法是向下递归，即找到初始节点的所有下级节点。

2.向上递归：从子节点开始向上找父节点

如何向上递归？
下边的sql实现了向上递归：

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限制执行时间，超过此时间也会终止递归操作。