gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-29 21:31:16 25 4
gpt4 key购买 nike

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

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'] ....
同样,孩子的等级也不知道它会有所不同。

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

最佳答案

如果您使用的是MySQL 8,请使用递归with子句:

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 8之前

对于不支持通用表表达式的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))


这是 fiddle

在这里,在 @pv := '19'中指定的值应设置为要选择其所有后代的父级的 id

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

查询中的变量分配

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


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


总而言之,人们可能会发现这些假设过于冒险,无法依靠。 documentation警告:


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


因此,即使它与上述查询一致地工作,评估顺序仍可能会更改,例如,当您添加条件或将此查询用作较大查询中的视图或子查询时。 will be removed in a future MySQL release是一个“特征”:


  MySQL的早期版本使在 SET以外的语句中为用户变量分配值成为可能。 MySQL 8.0支持此功能以实现向后兼容,但是在将来的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       | NAME
19 | category1
19/1 | category2
19/1/1 | category3
19/1/1/1 | category4


然后,您的 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/35240540/

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