使用MySQL查询遍历行制作递归树

2021-11-20 00:00:00 递归 mysql

我有一个这样设置的物料清单表:
项目 - 父

I have a bill of materials table that is set up like this:
item - parent

当我显示物料清单时的最终结果是它是这样显示的:

The end result when I display the bill of materials is that it is displayed like this: 

item 1  - parent 0    
    item 2 - parent 1    
    item 3 - parent 1

最终的结果也可能是多层次的:

The final result could also be multi level like this: 

item 3 - parent 0    
    item 4 - parent 3    
    item 76 - parent 3

它可以无限地持续下去:

And it can go on ad infinitum: 

item 76 - parent 0    
    item 46 - parent 76    

item 46 - parent 0     
    item 25 - parent 46

现在，我要么只从数据库中获得 1 个级别:

Right now, I either just get 1 level from the database:

SELECT * FROM bom WHERE parentId = $itemId (shorthand)

或者从表中提取每一行并使用我的递归函数来整理出我需要的那些，但这显然是低效的，因为我可能只需要 10 行，但我提取了 10,000 条记录.递归函数的输出将创建一个这样的树:

Or pull every row from the table and use my recursive function to sort out just the ones I need, but this is obviously inefficient as I may only need 10 rows, but I pull 10,000 records. The output of the recursive function will just create a tree like this:

item 1
   item 2
   item 3
      item 4
      item 76
         item 46
            item 25

我只知道我从第 1 项开始.第 5 项可以有 11 的父项；他们不必按顺序进行.我想获得树中的所有子分支.我怎么能在 mysql 中做这个查询?

All I know is that I am starting at item 1. Item 5 could have a parent of 11; they do not have to go sequential. I want to get all of the child branches in the tree. How could I do this query in mysql?

推荐答案

早在 2011 年 10 月 24 日，有人发布了 DBA StackExchange 中关于 MySQL 中的树遍历的问题.MySQL的SQL不支持.

Back in October 24, 2011, someone posted a question in the DBA StackExchange about tree traversal in MySQL. The SQL for MySQL cannot support it.

我在 <中编写了三 (3) 个存储过程(GetParentIDByID、GetAncestry 和 GetFamilyTree)我对这个问题的回答.希望这些信息能帮助您构建您正在寻找的内容.

I wrote up three(3) Stored Procedures (GetParentIDByID, GetAncestry and GetFamilyTree) in my answer to that question. Hope this information helps you construct what you are looking for.

相关文章