MySQL - 递归列出表中所有项的所有父级和祖先

Question

我有一个带有父子层次结构的表格，支持多个（理论上无限）嵌套级别：

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

查看Fiddle代码： http://sqlfiddle.com/#!9/48d276f/902/0

但此查询仅适用于给定的一个子级id（在本例中为31）。我未能将此查询扩展到整个表格，是否有一种方法可以在每个下一行中重置计数器变量？

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

在MySQL 8中，由于递归查询（感谢@GMB），这是可能的，但由于我们仍在运行MySQL 5.7，因此我想知道是否存在旧版本的解决方案。

- Stephen B

---

解决方案一定要是SQL查询吗？或者查询加上一些处理也可以吗？ - Aashish gaba

后处理当然可以接受，但我想避免多个查询。 - Stephen B

好的，这个问题有点类似。如果涉及到从根节点到叶子节点和从叶子节点到根节点的遍历，我已经为此编写了一个解决方案。https://stackoverflow.com/a/63120595/13257968 尽管它是用PHP编写的，但我认为这里的逻辑也可以使用。 - Aashish gaba

如果存在某些额外的关系，允许在严格处理其父节点之后处理节点（例如，当所有行的id < parent_id时），则可以理论上使用用户定义变量来解决该任务。如果没有，则只有存储过程可以帮助 - 我建议在任何情况下都使用此方法。 - Akina

Answer 1

如果您正在运行MySQL 8.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

DB Fiddle上的示例:

id | all_parents（所有父节点）
-: | :----------
 1 | 0          
 2 | 0          
 3 | 0          
17 | 3,0        
31 | 17,3,0     

- GMB

---

谢谢，这是一个很好的解决方案！但是，由于我们需要升级才能使用它，您是否知道是否也有可能使用MySQL 5.7的替代方案？我会更新我的版本并重新提问。 - Stephen B

我已经升级了版本，但仍然不支持递归....!!! - Engineer S. Saad

lvl来自哪里 - 当我尝试将其适应到我的情况时，它只会显示字段列表中的未知列lvl。 - Dan

@Dan：lvl来自CTE的非递归语句。它也可以写成"SELECT 1 AS lvl"，即你选择一个常量并给它一个别名（列名）。然后在递归语句中，你引用它并在每个级别上递增它。 - user323094

Answer 2

CREATE PROCEDURE make_csv_parent ()
BEGIN
    CREATE TABLE temp ( id INT PRIMARY KEY, parent_id INT, parents TEXT);
    INSERT INTO temp (id, parent_id, parents)
    SELECT id, parent_id, parent_id
    FROM menu
    WHERE parent_id = 0;
    WHILE ROW_COUNT() DO
        INSERT IGNORE INTO temp (id, parent_id, parents)
        SELECT menu.id, menu.parent_id, CONCAT(menu.parent_id, ',', temp.parents)
        FROM menu 
        JOIN temp ON menu.parent_id = temp.id;
    END WHILE;
    SELECT id, parents FROM temp;
    DROP TABLE temp;
END

fiddle

- Akina