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

Question

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

现在，我想有一个MySQL查询，我只需向它提供id（例如id=19)，然后我应该获得它的所有子id（即，结果应该有id'20、21、22']...

不知道孩子的等级；它可以变化....

我知道如何使用for循环来实现...但是如何使用单个MySQL查询实现相同的功能呢？

Answer 1

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

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.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 条件，否则结果将不完整。

这个查询使用特定的MySQL语法：变量在执行过程中被分配和修改。对执行顺序做了一些假设：

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

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

您可能会得到您期望的结果，但这不能保证[...]涉及用户变量的表达式的求值顺序未定义。

因此，即使它与上面的查询工作一致，计算顺序仍然可能会更改，例如，当您添加条件或将此查询用作更大查询中的视图或子查询时。这是一个“特性”，将在未来的MySQL版本中删除：

MySQL以前的版本允许在set以外的语句中为用户变量赋值。MySQL8.0支持此功能，以实现向后兼容性，但在MySQL的未来版本中可能会删除此功能。

如上所述，从MySQL8.0开始，您应该使用递归的with语法。

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

越来越多的数据库使用[RECURSIVE]语法实现SQL:1999 ISO标准用于递归查询（例如，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也支持它。有关要使用的语法，请参阅答案顶部。

有些数据库有一种可供选择的、用于分层查找的非标准语法，例如Oracle、DB2、Informix、CUBRID和其他数据库上可用的connect by子句。

MySQL5.7版本没有提供这样的功能。如果您的数据库引擎提供这种语法，或者您可以迁移到提供这种语法的数据库引擎，那么这无疑是最好的选择。如果不是，那么也考虑以下备选方案。

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