为分层数据创建递归 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']....

子级的层次结构未知；它可能会有所不同......

我知道如何使用 for 循环来做到这一点...但是如何使用单个 MySQL 查询来实现相同的目的？

Answer 1

对于MySQL 8+：使用递归使用语法。
对于 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

对于不支持公共表表达式的 MySQL 版本（最高版本 5.7），您可以使用以下查询来实现此目的：

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))

这是一个小提琴。

此处，@pv := '19' 中指定的值应设置为您要选择其所有后代的父级的 id。

如果父母有多个孩子，这也将起作用。但要求每条记录都满足parent_id < id< id条件，否则结果不完整。

查询内的变量赋值

该查询使用特定的 MySQL 语法：在执行期间分配和修改变量。对执行顺序做了一些假设：

• 首先评估 from 子句。这就是 @pv 初始化的地方。
• 按照从 from 别名检索的顺序对每条记录评估 where 子句。因此，这里设置的条件仅包括父级已被识别为位于后代树中的记录（主要父级的所有后代都将逐步添加到 @pv）。
• 此 where 子句中的条件按顺序求值，一旦总结果确定，求值就会中断。因此，第二个条件必须位于第二位，因为它将 id 添加到父列表中，并且只有在 id 通过第一个条件时才会发生这种情况。调用 length 函数只是为了确保此条件始终为真，即使 pv 字符串由于某种原因会产生虚假值。

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

因此，即使它与上述查询一致，评估顺序仍然可能会发生变化，例如，当您添加条件或将此查询用作较大查询中的视图或子查询时。这是一个将在未来的 MySQL 版本中删除的“功能” ：

如上所述，从 MySQL 8.0 开始，您应该使用递归 with 语法。

效率

对于非常大的数据集，此解决方案可能会变慢，因为 find_in_set 操作不是在列表中查找数字的最理想方式，当然也不是在达到与返回的记录数。

替代方案 1：使用递归，连接

越来越多的数据库实现 SQL:1999 ISO 标准 WITH [RECURSIVE]递归查询的 语法（例如 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+）。从 版本 8.0 开始，MySQL 也支持它。请参阅此答案的顶部以了解要使用的语法。

某些数据库具有用于分层查找的替代非标准语法，例如 CONNECT BY 子句/B19306_01/server.102/b14200/queries003.htm" rel="noreferrer">Oracle，DB2，Informix、CUBRID 和其他数据库。

MySQL 5.7 版不提供这样的功能。当您的数据库引擎提供此语法或者您可以迁移到提供此语法的数据库引擎时，那么这无疑是最佳选择。如果没有，请考虑以下替代方案。

替代方案 2：路径样式标识符

如果您分配包含分层信息（路径）的 id 值，事情就会变得容易得多。例如，在您的情况下，这可能如下所示：

ID	姓名
19	类别1
19/1	类别2
1/19	类别3
19/1/1/1	类别4

然后您的选择将如下所示：

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;

请参阅此小提琴

where 条件指定您要检索哪个父代的后代。您可以根据需要扩展此查询更多级别。