gpt4 book ai didi

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

转载 作者:IT老高 更新时间:2023-10-28 12:48:32 26 4
gpt4 key购买 nike

我有一个 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 查询实现相同的目标?

最佳答案

对于 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 recursiveconnect by越来越多的数据库实现 SQL:1999 ISO standard WITH [RECURSIVE] syntax为递归查询(例如 Postgres 8.4+SQL Server 2005+DB2Oracle 11gR2+SQLite 3.8.4+Firebird 2.1+H2HyperSQL 2.1.0+TeradataMariaDB 10.2.2+)。从 version 8.0, also MySQL supports it 开始。有关要使用的语法,请参阅此答案的顶部。
    某些数据库具有用于分层查找的替代性非标准语法,例如 OracleDB2InformixCUBRID 和其他数据库上可用的 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 条件指定要检索其后代的父级。您可以根据需要使用更多级别扩展此查询。

    关于mysql - 如何创建 MySQL 分层递归查询?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/20215744/

    26 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com