mysql - 如何创建 MySQL 分层递归查询？

Question

我有一个 MySQL 表，如下所示:


ID
名称
parent_id


19
类别1
0

20
类别2
19

21
类别3
20

22
类别4
21

...
...
...


现在，我想要一个 MySQL 查询，我只需向其提供 id [例如说 id=19 ] 那么我应该得到它所有的子 ID [即结果应该有 ID '20,21,22']....
child 的等级是未知的；它可以变化....
我知道如何使用 for循环...但是如何使用单个 MySQL 查询实现相同的目标？

Answer 1

对于 MySQL 8+: 使用递归 with 语法。
对于 MySQL 5.x: 使用内联变量、路径 ID 或自联接。
MySQL 8+

with recursive cte (id, name, parent_id) as (
  select     id,
             name,
             parent_id
  from       products
  where      parent_id = 19
  union all
  select     p.id,
             p.name,
             p.parent_id
  from       products p
  inner join cte
          on p.parent_id = cte.id
)
select * from cte;

parent_id = 19 中指定的值应设置为要选择其所有后代的父级的 id。
MySQL 5.x
对于不支持公用表表达式(最高版本 5.7)的 MySQL 版本，您可以使用以下查询来实现:

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))

这是一个 fiddle 。
此处， @pv := '19' 中指定的值应设置为要选择其所有后代的父级的 id。
如果 parent 有多个 child ，这也将起作用。但是，要求每条记录都满足条件 parent_id < id ，否则结果将不完整。
查询中的变量赋值
此查询使用特定的 MySQL 语法:在执行期间分配和修改变量。对执行顺序做了一些假设:

首先评估 from 子句。所以这就是 @pv 被初始化的地方。

where 子句按照从 from 别名中检索的顺序对每条记录进行评估。因此，这是放置条件以仅包括父代已被识别为在后代树中的记录(主要父代的所有后代逐渐添加到 @pv )。

where 子句中的条件按顺序进行评估，一旦总结果确定，则中断评估。因此，第二个条件必须排在第二位，因为它将 id 添加到父列表中，并且只有在 id 通过第一个条件时才会发生这种情况。调用 length 函数只是为了确保此条件始终为真，即使 pv 字符串由于某种原因会产生假值。

总而言之，人们可能会发现这些假设风险太大而无法依赖。 documentation 警告:

you might get the results you expect, but this is not guaranteed [...] the order of evaluation for expressions involving user variables is undefined.

因此，即使它与上述查询一致，评估顺序仍可能发生变化，例如，当您添加条件或将此查询用作更大查询中的 View 或子查询时。 will be removed in a future MySQL release 是一个“功能”:

Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.

如上所述，从 MySQL 8.0 开始，您应该使用递归 with 语法。
效率
对于非常大的数据集，此解决方案可能会变慢，因为 find_in_set 操作不是在列表中查找数字的最理想方法，当然不是在大小达到与返回的记录数量相同数量级的列表中.
备选方案 1: with recursive 、 connect by越来越多的数据库实现 SQL:1999 ISO standard WITH [RECURSIVE] syntax为递归查询(例如 Postgres 8.4+， SQL Server 2005+， DB2， Oracle 11gR2+， SQLite 3.8.4+， Firebird 2.1+， H2， HyperSQL 2.1.0+， Teradata， MariaDB 10.2.2+)。从 version 8.0, also MySQL supports it 开始。有关要使用的语法，请参阅此答案的顶部。
某些数据库具有用于分层查找的替代性非标准语法，例如 Oracle 、 DB2 、 Informix 、 CUBRID 和其他数据库上可用的 CONNECT BY 子句。
MySQL 5.7 版没有提供这样的功能。如果您的数据库引擎提供了这种语法，或者您可以迁移到提供这种语法的引擎，那么这无疑是最好的选择。如果不是，那么还可以考虑以下替代方案。
备选方案 2:路径式标识符
如果您分配包含层次结构信息的 id 值:路径，事情会变得容易得多。例如，在您的情况下，这可能如下所示:


ID
名称


19
类别1

19/1
类别2

19/1/1
类别3

19/1/1/1
类别4


那么您的 select 将如下所示:

select  id,
        name 
from    products
where   id like '19/%'

备选方案 3:重复自联接
如果您知道层次结构树的深度上限，则可以使用标准的 sql 查询，如下所示:

select      p6.parent_id as parent6_id,
            p5.parent_id as parent5_id,
            p4.parent_id as parent4_id,
            p3.parent_id as parent3_id,
            p2.parent_id as parent2_id,
            p1.parent_id as parent_id,
            p1.id as product_id,
            p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id 
left join   products p3 on p3.id = p2.parent_id 
left join   products p4 on p4.id = p3.parent_id  
left join   products p5 on p5.id = p4.parent_id  
left join   products p6 on p6.id = p5.parent_id
where       19 in (p1.parent_id, 
                   p2.parent_id, 
                   p3.parent_id, 
                   p4.parent_id, 
                   p5.parent_id, 
                   p6.parent_id) 
order       by 1, 2, 3, 4, 5, 6, 7;

看到这个 fiddle where 条件指定要检索其后代的父级。您可以根据需要使用更多级别扩展此查询。