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

2021-11-20 00:00:00 sql hierarchical-data mysql recursive-query

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

<头>

id	名称	parent_id
19	category1	0
20	category2	19
21	category3	20
22	category4	21
...	...	...

现在，我想要一个单一的 MySQL 查询，我只需提供 ID [例如说 id=19] 然后我应该得到它的所有子 ID [即结果应该有 ID '20,21,22']....

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

我知道如何使用 for 循环来实现...但如何使用单个 MySQL 查询实现相同的效果?

解决方案

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

MySQL 8+

 with recursive cte (id, name, parent_id) as (选择身份证，姓名，parent_id从产品其中 parent_id = 19联合所有选择 p.id，p.name,p.parent_id从产品 p内连接p.parent_id = cte.id)从 cte 中选择 *；

parent_id = 19 中指定的值应设置为要选择其所有后代的父级的 id.

MySQL 5.x

对于不支持 Common Table Expressions 的 MySQL 版本(最高版本 5.7)，您可以使用以下查询来实现:

选择id，姓名，parent_id来自(从产品中选择*按 parent_id 排序，id) products_sorted，(选择@pv := '19')初始化其中 find_in_set(parent_id, @pv)和长度(@pv := concat(@pv, ',', id))

这是一个小提琴.

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

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

查询中的变量赋值

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

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

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

<块引用>

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

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

<块引用>

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

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

效率

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

方案一:`with recursive`，`connect by`

越来越多的数据库实现了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	category2
19/1/1	category3
19/1/1/1	category4

然后您的 select 将如下所示:

选择id，姓名从产品其中 id 像 '19/%'

备选方案 3:重复自联接

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

选择 p6.parent_id 作为 parent6_id，p5.parent_id 作为 parent5_id，p4.parent_id 作为 parent4_id，p3.parent_id 作为 parent3_id，p2.parent_id 作为 parent2_id，p1.parent_id 作为 parent_id，p1.id 作为 product_id，p1.name来自产品 p1在 p2.id = p1.parent_id 上左连接产品 p2在 p3.id = p2.parent_id 上左连接产品 p3在 p4.id = p3.parent_id 上左连接产品 p4在 p5.id = p4.parent_id 上左连接产品 p5在 p6.id = p5.parent_id 上左连接产品 p6其中 19 in (p1.parent_id,p2.parent_id,p3.parent_id,p4.parent_id，p5.parent_id，p6.parent_id)按 1、2、3、4、5、6、7 排序；

见这个fiddle

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

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

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']....

The hierarchy of the children is not known; it can vary....

I know how to do it using a for loop... but how to achieve the same using a single MySQL query?

解决方案

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

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;

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.x

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

Here is a fiddle.

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

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.

Variable assignments inside a query

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

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.

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:

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.

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

Efficiency

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.

Alternative 1: `with recursive`, `connect by`

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.

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

Alternative 2: Path-style Identifiers

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

Then your select would look like this:

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

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;

See this fiddle

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

相关文章

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

MySQL 8+

MySQL 5.x

查询中的变量赋值

效率

方案一:with recursive，connect by

备选方案 2:路径样式标识符

备选方案 3:重复自联接

MySQL 8+

MySQL 5.x

Variable assignments inside a query

Efficiency

Alternative 1: with recursive, connect by

Alternative 2: Path-style Identifiers

Alternative 3: Repeated Self-joins

方案一:`with recursive`，`connect by`

Alternative 1: `with recursive`, `connect by`