Mysql树形表的两种查询方案（递归与自连接）

前言

有时候我们要查询数据库数据，例如课程分类表，是一个树形结构，一张表就实现了一对多的关系，并且表中的数据存在的关系是”爷爷-儿子-孙子....”的关系，如图所示：其中parentid字段为父结点ID，它是树型结构的标志字段。

解决方案

对于这样的表很显然想要通过查询来实现价值绝对是不能只靠select * from table 来实现的，下面提供两种解决方案：

1.自链接

如果树的层级固定可以使用表的自链接去查询，inner join 关键可以实现多种分类的查询。比如可以用下边的SQL：

SELECT
    one.id one_id,
    one.label one_label,
    two.id two_id,
    two.label two_label,
    three.id three_id,
    three.label three_label
  FROM
      course_category one
      INNER JOIN course_category two ON two.parentid=one.id
      INNER JOIN course_category three ON three.parentid=two.id
 WHERE one.id='1' AND one.is_show='1' AND two.is_show='1'
 ORDER BY one.orderby,two.orderby

 结果如下图：

这种查询的原则就是通过parentId去实现，“爷爷找爸爸，爸爸找儿子，儿子找孙子”

2.自联接弊端：

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

所以，如果树的层级不确定，此时可以使用MySQL递归实现，使用with语法

3.递归

注意：mysql的版本要大于等于8，否则不支持递归查询函数

下边是一个递归的简单例子：

	with RECURSIVE t1 AS(
		SELECT 1 AS n
		union all
		SELECT n+1 FROM t1 WHERE n<5
	)
	SELECT * from t1

解析：

WITH RECURSIVE t1 AS：
递归查询的开始，创建了一个名为t1的递归表。
SELECT 1 AS n：
在t1表中，插入了一个初始行，值为1，命名为n。
UNION ALL：
使用UNION ALL运算符将初始行和递归查询结果合并，形成递归步骤。这也就是下次递归的起点表
SELECT n+1 FROM t1 WHERE n<5：
递归部分的查询，从t1表中选择n加1的结果，当n小于5时进行递归。
SELECT * FROM t1：
最终查询，返回t1表的所有行。

结果如下：

下边我们使用递归实现课程分类的查询

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

查询结果如下：