树形表查询

针对于数据库中的树形表：

例如该表，这里有两种字段，一个自身id，一个父节点id(parentid)。

对于此类树形表，我们一般有两种查询方式。

自连接查询

树的层级固定时

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

SELECT * FROM course_category as one INNER JOIN course_category AS two
ORDER BY one.id

也可以一些过滤条件：

select
       one.id            one_id,
       one.name          one_name,
       one.parentid      one_parentid,
       one.orderby       one_orderby,
       one.label         one_label,
       two.id            two_id,
       two.name          two_name,
       two.parentid      two_parentid,
       two.orderby       two_orderby,
       two.label         two_label
   from course_category one
            inner join course_category two on one.id = two.parentid
   where one.parentid = 1
     and one.is_show = 1
     and two.is_show = 1
   order by one.orderby,
            two.orderby

更灵活的方式

        注意，这种自连接的方式一般适用于整个树形结构的层级已经确定。如果不知道该树的层级结构，也可以通过MySQL中的递归查询实现。

在MySQL8中，可以使用with语法，如下：

    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 as n 相当于这个表的初始值，把n设置为1，这里使用 UNION ALL 不断将每次递归得到的数据加入到表中。
• 比如说：初始时n为1，第一次递归，查到n为1，满足n < 5 就把n设置为2，由于UNION ALL，把结果n为2放到表中。现在n = 2。
• 第二次递归来了，类似于上述逻辑，又把n设置为3。
• 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'的所有下级节点，下级节点包括了所有层级的节点。
• 上边这种方法是向下递归，即找到初始节点的所有下级节点。

如何向上递归？

下边的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限制执行时间，超过此时间也会终止递归操作。
• mysql递归相当于在存储过程中执行若干次sql语句，java程序仅与数据库建立一次链接执行递归操作，所以只要控制好递归深度，控制好数据量性能就没有问题。

思考：如果java程序在递归操作中连接数据库去查询数据组装数据，这个性能高吗？

答：肯定是不高的。如果是在MySQL中递归操作，只需要连接一次数据库。而如果在Java程序中递归操作中连接数据库，每次递归都会连接一次数据库，非常消耗性能。