现在MySQL 8.0支持递归查询, 我们可以说所有流行的SQL数据库都支持标准语法下的递归查询。
WITH RECURSIVE MyTree AS (
SELECT * FROM MyTable WHERE ParentId IS NULL
UNION ALL
SELECT m.* FROM MyTABLE AS m JOIN MyTree AS t ON m.ParentId = t.Id
)
SELECT * FROM MyTree;
我在2017年的演示中测试了MySQL 8.0中的递归查询Recursive Query Throwdown。
以下是我2008年的原始答案：
在关系数据库中存储树形数据有几种方法。你在示例中展示了两种方法：
- 相邻列表（“parent”列）和
- 路径枚举（名称列中的点号数字）。
另一种解决方案称为嵌套集，它也可以存储在同一张表中。阅读Joe Celko的《SQL for Smarties中的树和层次结构》以获取更多关于这些设计的信息。
我通常更喜欢使用称为闭包表（也称为“相邻关系”）的设计来存储树形数据。它需要另一张表，但查询树很容易。
我在我的演示文稿 使用SQL和PHP模型处理分层数据 和我的书籍 SQL Antipatterns Volume 1：避免数据库编程陷阱 中介绍了闭包表。
CREATE TABLE ClosureTable (
ancestor_id INT NOT NULL REFERENCES FlatTable(id),
descendant_id INT NOT NULL REFERENCES FlatTable(id),
PRIMARY KEY (ancestor_id, descendant_id)
);
在闭包表中存储所有路径，其中一个节点直接从另一个节点继承。为每个节点包括一行引用自身的记录。例如，使用您在问题中展示的数据集：
INSERT INTO ClosureTable (ancestor_id, descendant_id) VALUES
(1,1), (1,2), (1,4), (1,6),
(2,2), (2,4),
(3,3), (3,5),
(4,4),
(5,5),
(6,6);
现在您可以像这样从节点1开始获取一棵树：
SELECT f.*
FROM FlatTable f
JOIN ClosureTable a ON (f.id = a.descendant_id)
WHERE a.ancestor_id = 1;
输出结果（在MySQL客户端中）如下所示：
+
| id |
+
| 1 |
| 2 |
| 4 |
| 6 |
+
换句话说，节点3和5被排除在外，因为它们属于一个单独的层级结构，不是从节点1下降的。
关于直接子代（或直接父代）的评论，您可以在 ClosureTable 中添加一个 "path_length" 列，以便更轻松地查询特定的直接子代或父代（或任何其他距离）。
INSERT INTO ClosureTable (ancestor_id, descendant_id, path_length) VALUES
(1,1,0), (1,2,1), (1,4,2), (1,6,1),
(2,2,0), (2,4,1),
(3,3,0), (3,5,1),
(4,4,0),
(5,5,0),
(6,6,0);
然后，您可以在搜索中添加一个术语，以查询给定节点的直接子代。这些是路径长度为1的后代。
SELECT f.*
FROM FlatTable f
JOIN ClosureTable a ON (f.id = a.descendant_id)
WHERE a.ancestor_id = 1
AND path_length = 1;
+
| id |
+
| 2 |
| 6 |
+
针对@ashraf的评论："如何按名称对整个树进行排序？"
以下是一个示例查询，返回所有作为节点1的后代的节点，将它们与包含其他节点属性（例如name）的FlatTable连接，并按名称排序。
SELECT f.name
FROM FlatTable f
JOIN ClosureTable a ON (f.id = a.descendant_id)
WHERE a.ancestor_id = 1
ORDER BY f.name;
来自@Nate的评论：
SELECT f.name, GROUP_CONCAT(b.ancestor_id order by b.path_length desc) AS breadcrumbs
FROM FlatTable f
JOIN ClosureTable a ON (f.id = a.descendant_id)
JOIN ClosureTable b ON (b.descendant_id = a.descendant_id)
WHERE a.ancestor_id = 1
GROUP BY a.descendant_id
ORDER BY f.name
+
| name | breadcrumbs |
+
| Node 1 | 1 |
| Node 1.1 | 1,2 |
| Node 1.1.1 | 1,2,4 |
| Node 1.2 | 1,6 |
+
今天有一个用户提出了一个编辑建议。SO主管批准了这个编辑，但我正在撤销它。
该编辑建议上面的最后一个查询中的ORDER BY应该是
ORDER BY b.path_length, f.name，可能是为了确保排序与层次结构匹配。但这行不通，因为它会将“节点1.1.1”排在“节点1.2”之后。
如果您想让排序方式以合理的方式匹配层次结构，则有可能实现，但不能仅通过路径长度排序。例如，请参见我的答案
MySQL Closure Table hierarchical database - How to pull information out in the correct order。
