MySQL-递归列出表中所有项的所有父项和祖先项 [英] MySQL - Recursively list all parents and ancestors of all items in table

问题描述

我有一个具有父/子层次结构的表，它支持多个(理论上是无限的)嵌套级别：

|------|-------------------|-------------|
|  id  |       title       |  parent_id  |
|------|-------------------|-------------|
|  1   |    Dashboard      |      0      |
|  2   |    Content        |      0      |
|  3   |    Modules        |      0      |
|  17  |    User Modules   |      3      |
|  31  |    Categories     |      17     |
|  ... |                   |             |
|------|-------------------|-------------|

我正在尝试构建一个查询，该查询生成每个项目的父项的串联列表，直到树中最高的父项：

|------|----------------------|
|  id  | concatenatedParents  |
|------|----------------------|
|  1   |  0                   |
|  2   |  0                   |
|  3   |  0                   |
|  17  |  3,0                 |
|  31  |  17,3,0              |
|  ... |                      |
|------|----------------------|

根据这里的许多其他答案，我构造了以下MySQL查询：

SELECT parentsTable._id, GROUP_CONCAT(parentsTable.parent_id SEPARATOR ',') as concatenatedParents FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 31, @l := 0) vars,
        menu m
    WHERE @r <> 0
) as parentsTable

请在此处查看小提琴：http://sqlfiddle.com/#!9/48d276f/902/0

但是该查询只适用于一个给定子ID(在本例中为31)。我未能成功展开整个表的此查询，是否有方法重置表中每隔一行的计数器变量？

我看到许多答案建议使用固定数量的联接，但接受可变数量级别的解决方案会更可取。

在MySQL8中，这要归功于递归查询(谢谢@gmb)，但由于我们仍在MySQL5.7上运行，我很感兴趣是否也有针对旧版本的解决方案。

推荐答案

如果您运行的是MySQL8.0，则最好使用递归查询来解决此问题：

with recursive cte as (
    select id, parent_id, 1 lvl from mytable
    union all
    select c.id, t.parent_id, lvl + 1
    from cte c
    inner join mytable t on t.id = c.parent_id
)
select id, group_concat(parent_id order by lvl) all_parents
from cte
group by id

Demo on DB Fiddle：

id | all_parents
-: | :----------
 1 | 0          
 2 | 0          
 3 | 0          
17 | 3,0        
31 | 17,3,0     

这篇关于MySQL-递归列出表中所有项的所有父项和祖先项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！