如何创建MySQL分层递归查询？
如何创建MySQL分层递归查询？
提问于 2018-02-26 06:18:16
我有一个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’。
如果可能的话，让我知道如何使用一个MySQL查询来实现相同的结果。
Stack Overflow用户
发布于 2018-02-26 14:49:50
对于不支持公共表表达式(直到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) > 0
and @pv := concat(@pv, ',', id)对于非常大的数据集，此解决方案可能会变慢，因为find_in_set操作不是在列表中查找数字的最理想方法。
备选案文1：查询如下所示：
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;有些数据库有一种用于分层查找的非标准语法，例如CONNECT BY子句在Oracle数据库中可用。DB2还支持这种替代语法。
MySQLVersion 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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100007441
复制相关文章
相似问题