MySQL - 递归树结构
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2378678/
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
MySQL - Recursing a tree structure
提问by Extrakun
I have a database table which link locations together; a location can be in a location, which can be inside another location.
我有一个将位置链接在一起的数据库表；一个位置可以在一个位置中，该位置可以在另一个位置内。
location (<id>, ....)
location_parent (<location_id>, <parent_id>)
Here's the MySQL/PHP to go down for a depth of one:
这是 MySQL/PHP 的深入分析：
$sql = "SELECT id FROM se_locations_services WHERE parent_locationid IN
( SELECT location_id FROM se_locations_parent WHERE parent_id = '$locationid' )";
How do I, given a parent location, gets all its descendants locations, no matter how deep, just using MySQL?
给定父位置，我如何仅使用 MySQL 获取其所有后代位置，无论多深？
回答by Pekka
There's a good-looking articleover at mysql.com outlining various ways of managing hierarchical data. I think it provides a full solution to your question, and shows various less simple, but faster approaches (e.g. Nested Sets).
mysql.com 上有一篇很好看的文章，概述了管理分层数据的各种方法。我认为它为您的问题提供了完整的解决方案，并展示了各种不太简单但速度更快的方法（例如嵌套集）。
回答by Eimantas
Since mysql statements can return only table-structured data, how do you imagine the returned tree-structure?
既然mysql语句只能返回表结构的数据，那么你如何想象返回的树结构呢？
It is possible to do a selection with [parent_id, child_id] table, but it requires temporary table and I've seen it done on DB2, not on MySQL.
可以使用 [parent_id, child_id] 表进行选择，但它需要临时表，而且我已经看到它在 DB2 上完成，而不是在 MySQL 上。
Check this article for implementation on tree-like structures stored in MySQL: http://articles.sitepoint.com/article/hierarchical-data-database/
检查这篇文章以了解存储在 MySQL 中的树状结构的实现：http: //articles.sitepoint.com/article/hierarchical-data-database/
回答by deltragon
This is an old question, but since i stumbled over this searching for a solution.
这是一个老问题，但因为我偶然发现了这个寻找解决方案。
Since MySQL 8.0, you can use a recursive CTEfor this:
从 MySQL 8.0 开始，您可以为此使用递归 CTE：
WITH RECURSIVE tmp (id) AS
(
SELECT id
FROM locations
WHERE parent_id IS NULL
UNION ALL
SELECT l.id
FROM tmp AS p JOIN locations AS l
ON p.id = l.parent_id
)
SELECT * FROM tmp
ORDER BY id;
This assumes a slightly different DB structure than in the original question (eg. there is only one table containing both parents/children), however, I am sure that this technique applies there as well.
这假设与原始问题中的数据库结构略有不同（例如，只有一个包含父/子的表），但是，我确信这种技术也适用于那里。
