使用MySQL查询遍历行以创建递归树 [英] Using MySQL query to traverse rows to make a recursive tree

问题描述

我有一个物料清单表，其设置如下:
项-父项

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    

它可以无限进行:

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；项目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日，有人发布了 strong>有关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.

我在 <我对这个问题的回答很强 .希望这些信息可以帮助您构建所需的内容.

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.

这篇关于使用MySQL查询遍历行以创建递归树的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！