如何创建MySQL分层递归查询 [英] How to create a MySQL hierarchical recursive query

问题描述

我有一个如下的MySQL表:

I have a MySQL table which is as follows:

id | name        | parent_id
19 | category1   | 0
20 | category2   | 19
21 | category3   | 20
22 | category4   | 21
......

现在，我想有一个简单的MySQL查询，只需向其提供ID [例如说'ID = 19']，然后就应该获取其所有子ID [即结果应具有ID '20，21,22'] .... 此外，孩子的等级也不知道它会有所不同....

Now, I want to have a single MySQL query to which I simply supply the id [for instance say 'id = 19'] then I should get all its child ids [i.e. result should have ids '20,21,22'].... Also, the hierarchy of the children is not known it can vary....

此外，我已经有了使用for循环的解决方案.....如果可能的话，让我知道如何使用单个MySQL查询来实现相同的解决方案.

Also, I already have the solution using the for loop..... Let me know how to achieve the same using a single MySQL query if possible.

推荐答案

对于 MySQL 8 +::使用递归

For MySQL 8+: use the recursive with syntax.
For MySQL 5.x: use inline variables, path IDs, or self-joins.

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.

The value specified in parent_id = 19 should be set to the id of the parent you want to select all the descendants of.

对于不支持通用表表达式的MySQL版本(最高5.7版)，您可以通过以下查询来实现:

For MySQL versions that do not support Common Table Expressions (up to version 5.7), you would achieve this with the following query:

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.

Here, the value specified in @pv := '19' should be set to the id of the parent you want to select all the descendants of.

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

This will work also if a parent has multiple children. However, it is required that each record fulfills the condition parent_id < id, otherwise the results will not be complete.

此查询使用特定的MySQL语法:在执行过程中分配和修改变量.对执行顺序进行了一些假设:

This query uses specific MySQL syntax: variables are assigned and modified during its execution. Some assumptions are made about the order of execution:

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

• The from clause is evaluated first. So that is where @pv gets initialised.
• The where clause is evaluated for each record in the order of retrieval from the from aliases. So this is where a condition is put to only include records for which the parent was already identified as being in the descendant tree (all descendants of the primary parent are progressively added to @pv).
• The conditions in this where clause are evaluated in order, and the evaluation is interrupted once the total outcome is certain. Therefore the second condition must be in second place, as it adds the id to the parent list, and this should only happen if the id passes the first condition. The length function is only called to make sure this condition is always true, even if the pv string would for some reason yield a falsy value.

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

All in all, one may find these assumptions too risky to rely on. The documentation warns:

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

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

因此，即使它与上面的查询一致地工作，评估顺序仍可能会更改，例如，当您添加条件或将此查询用作较大查询中的视图或子查询时.这是一个功能"，将在将来的MySQL版本中删除:

So even though it works consistently with the above query, the evaluation order may still change, for instance when you add conditions or use this query as a view or sub-query in a larger query. It is a "feature" that will be removed in a future MySQL release:

MySQL的先前版本使在SET以外的语句中为用户变量分配值成为可能. MySQL 8.0支持此功能以实现向后兼容，但是在将来的MySQL版本中可能会删除该功能.

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语法.

As stated above, from MySQL 8.0 onward you should use the recursive with syntax.

对于非常大的数据集，此解决方案可能会变慢，因为

For very large data sets this solution might get slow, as the find_in_set operation is not the most ideal way to find a number in a list, certainly not in a list that reaches a size in the same order of magnitude as the number of records returned.

越来越多的数据库实现 SQL:1999 ISO标准WITH [RECURSIVE]语法用于递归查询(例如 Postgres 8.4+ ， SQL Server 2005+ ， SQLite 3.8.4+ ， Firebird 2.1+ ， H2 ，版本8.0开始，MySQL也支持它.有关使用的语法，请参见此答案的顶部.

More and more databases implement the SQL:1999 ISO standard WITH [RECURSIVE] syntax for recursive queries (e.g. 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+). And as of version 8.0, also MySQL supports it. See the top of this answer for the syntax to use.

某些数据库具有用于分层查找的替代非标准语法，例如 DB2 ， Informix ， CUBRID 和其他数据库.

Some databases have an alternative, non-standard syntax for hierarchical look-ups, such as the CONNECT BY clause available on Oracle, DB2, Informix, CUBRID and other databases.

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

MySQL version 5.7 does not offer such a feature. When your database engine provides this syntax or you can migrate to one that does, then that is certainly the best option to go for. If not, then also consider the following alternatives.

如果您要分配包含层次结构信息(路径)的id值，事情将变得容易得多.例如，在您的情况下，可能看起来像这样:

Things become a lot easier if you would assign id values that contain the hierarchical information: a path. For example, in your case this could look like this:

ID       | NAME
19       | category1   
19/1     | category2  
19/1/1   | category3  
19/1/1/1 | category4  

然后您的select看起来像这样:

Then your select would look like this:

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

替代3:重复的自我联接

如果您知道层次结构树的深度上限，则可以使用标准的sql查询，如下所示:

Alternative 3: Repeated Self-joins

If you know an upper limit for how deep your hierarchy tree can become, you can use a standard sql query like this:

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条件指定要检索其后代的父级.您可以根据需要将查询扩展到更多级别.

The where condition specifies which parent you want to retrieve the descendants of. You can extend this query with more levels as needed.

这篇关于如何创建MySQL分层递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！