MySQL 使用MySQL查询遍历行制作递归树
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10646833/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using MySQL query to traverse rows to make a recursive tree
提问by phpmeh
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
Right now, I either just get 1 level from the database:
现在，我要么只从数据库中获得 1 个级别：
SELECT * FROM bom WHERE parentId = $itemId (shorthand)
SELECT * FROM bom WHERE parentId = $itemId (shorthand)
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:
或者从表中提取每一行并使用我的递归函数来整理出我需要的那些，但这显然是低效的，因为我可能只需要 10 行，但我提取了 10,000 条记录。递归函数的输出将创建一个这样的树：
item 1
item 2
item 3
item 4
item 76
item 46
item 25
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?
我所知道的是我从第 1 项开始。第 5 项可以有 11 的父项；他们不必按顺序进行。我想获得树中的所有子分支。我怎么能在mysql中做这个查询？
回答by RolandoMySQLDBA
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.
早在 2011 年 10 月 24 日，有人在 DBA StackExchange 中发布了一个关于 MySQL 中的树遍历的问题。MySQL 的 SQL 不支持它。
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.
我在这个问题的回答中写了三 (3) 个存储过程（GetParentIDByID、GetAncestry 和 GetFamilyTree）。希望这些信息可以帮助您构建您正在寻找的内容。
回答by Mark Byers
Bill Karwinhas posted a slide show about heirarchical datain MySQL. If changing your database design is an option, there are some other appealing ways to store your data to make it easier to query. The approaches he covers are:
Bill Karwin发布了一个关于MySQL 中的分层数据的幻灯片。如果更改数据库设计是一种选择，那么还有一些其他吸引人的方法来存储数据以使其更易于查询。他涵盖的方法是：
- Adjacency List
- Path Enumeration
- Nested Sets
- Closure Table
- 邻接表
- 路径枚举
- 嵌套集
- 收尾表
Slide 69 has a nice table showing the pros and cons of each method, so I suggest you look at that slide first to see which approach might work for you, then go back and look at the details of how to implement it. Note that the design you have chosen (adjacency list) is the onlyone of the four designs presented that makes it hard to query a subtree.
幻灯片 69 有一个很好的表格，显示了每种方法的优缺点，所以我建议你先看这张幻灯片，看看哪种方法可能适合你，然后再回头看看如何实施它的细节。请注意，您选择的设计（邻接表）是所呈现的四种设计中唯一一种难以查询子树的设计。
Having said that, if you can't change your design or you want to stick with the adjacency list then I have to agree with Didier that you should take a look at Quassnoi's article "Hierarchical queries in MySQL". It is a very clear article and explains how to write the query efficiently.
话虽如此，如果你不能改变你的设计，或者你想坚持使用邻接表，那么我必须同意 Didier 的意见，你应该看看Quassnoi的文章“MySQL 中的分层查询”。这是一篇非常清晰的文章，解释了如何有效地编写查询。
回答by Didier Spezia
AFAIK, it is non trivial to do this with MySQL.
AFAIK，使用 MySQL 执行此操作并非易事。
Here is a good set of articles about it:
这里有一组关于它的好文章：
http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/
http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/
回答by Uluaiv
MySQL (8) nowadays supports recursive queries.
MySQL (8) 现在支持递归查询。
Considering your table item(id, parent) and a starting item with id = 1, the following would do the job:
考虑到您的表 item(id, parent) 和一个 id = 1 的起始项，以下将完成这项工作：
with recursive result(id, parent) as (select id, parent from item where id = 1 union all select i.id, i.parent from item i join result on i.parent = result.id) select * from result;
