使用MySQL在一次查询中获取breadcrumb目录序列

Question

我应该根据文件夹id获得文件夹的breadcrumb或目录层次结构。

ID   title      parent_id
1   Home          0
2   First         1
3   Second        2
4   Another       0
5   Test          4

根据上面的表格，如果我的当前文件夹是Second，那么层次结构应该是家比;第一次在第二个

因此，我期望的结果是:

ID   title      parent_id
1   Home          0
2   First         1
3   Second        2

案例2:如果id为5，则预期结果为:

4   Another       0
5   Test          4

我下面的查询只产生第四个深度。

SELECT *
FROM folders AS t1
LEFT JOIN folders AS t2 ON t1.parent_id = t2.id
LEFT JOIN folders AS t3 ON t2.parent_id = t3.id
LEFT JOIN folders AS t4 ON t3.parent_id = t4.id

是否有其他方法来生成到n深度的完整层次结构?

Answer 1

在mysql 8或mariadb 10.2+中，您可以使用递归公共表表达式:

with recursive folder_hierarchy as (
select f.id,f.title,f.parent_id,0 as level from folder f where f.id=3
union all
select pf.id,pf.title,pf.parent_id,level+1 from folder_hierarchy fh join folder f on f.id=fh.id join folder pf on pf.id=f.parent_id
)
select id,title,parent_id from folder_hierarchy
order by level desc

小提琴

但是，我建议使用null而不是0来表示没有父节点。