mysql - 在我的sql <8并且没有CTE中获取树文件夹结构的 parent 和子项

我有一个文件夹表，它通过一个关系连接到自己：

CREATE TABLE folders (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  title nvarchar(255) NOT NULL,
  parent_id int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO folders(id, title, parent_id) VALUES(1, 'root', null);
INSERT INTO folders(id, title, parent_id) values(2, 'one', 1);
INSERT INTO folders(id, title, parent_id) values(3, 'target', 2);
INSERT INTO folders(id, title, parent_id) values(4, 'child one', 3);
INSERT INTO folders(id, title, parent_id) values(5, 'child two', 3);
INSERT INTO folders(id, title, parent_id) values(6, 'root 2', null);
INSERT INTO folders(id, title, parent_id) values(7, 'other child one', 6);
INSERT INTO folders(id, title, parent_id) values(8, 'other child two', 6);

我需要一个返回该记录的所有父级、返回路由和任何子级的查询。
因此，如果我要求文件夹与id，我得到记录：parent_id。我一直在找父母。
MySQL的版本是5.7，目前还没有升级计划，所以遗憾的是CTE不是一个选项。
我创造了这个sql fiddle

最佳答案

在MySQL8.0中，您可以使用Recursive Common Table Expressions来处理这个用例。
以下查询为您提供给定记录（包括该记录本身）的父级：

with recursive parent_cte (id, title, parent_id) as (
  select id, title, parent_id
  from folders
  where id = 3
  union all
  select  f.id, f.title, f.parent_id
  from folders f
  inner join parent_cte pc on f.id = pc.parent_id
)
select * from parent_cte;

| id  | title  | parent_id |
| --- | ------ | --------- |
| 3   | target | 2         |
| 2   | one    | 1         |
| 1   | root   |           |

And here is a slightly different query, that returns the children tree of a given record:

with recursive children_cte (id, title, parent_id) as (
  select id, title, parent_id
  from folders
  where parent_id = 3
  union all
  select  f.id, f.title, f.parent_id
  from folders f
  inner join children_cte cc on f.parent_id = cc.id
)
select * from children_cte;

| id  | title     | parent_id |
| --- | --------- | --------- |
| 4   | child one | 3         |
| 5   | child two | 3         |

Both queriers can be combined as follows:

with recursive parent_cte (id, title, parent_id) as (
  select id, title, parent_id
  from folders
  where id = 3
  union all
  select  f.id, f.title, f.parent_id
  from folders f
  inner join parent_cte pc on f.id = pc.parent_id
),
children_cte (id, title, parent_id) as (
  select id, title, parent_id
  from folders
  where parent_id = 3
  union all
  select  f.id, f.title, f.parent_id
  from folders f
  inner join children_cte cc on f.parent_id = cc.id
)
select * from parent_cte
union all select * from children_cte;

| ID标题父项ID|
|---|——————————————|
| 3目标2|
| 2 1 1|
| 1根||
| 4儿童1 3|
| 5儿童2 3|
Demo on DB Fiddle

关于mysql - 在我的sql <8并且没有CTE中获取树文件夹结构的 parent 和子项，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55288840/