如何在SQL中存储树形结构？

Question

这是我使用sqlite的模式图，我不确定这是否是在SQL中创建树形结构的好方法，因为我必须多次遍历才能得到整个树形结构，而不是基于顶部注释提取整个树形结构并在Python中构建树形结构。请问有人可以给我一些建议吗？

BEGIN;
CREATE TABLE "tree_comment" 
    ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
     "text" text NOT NULL, 
     "parent_id" integer NULL REFERENCES "tree_comment" ("id"));
CREATE INDEX "tree_comment_6be37982" ON "tree_comment" ("parent_id");

COMMIT;

- Kevin

---

你需要创建扁平表，并使用外键关系来描述节点（行）之间的关系。如果要以树形表示进行分派，请使用Join或其他方法。请查看这个MySQL问题和答案mysql-tree-hierarchical-query。 - Grijesh Chauhan

如果你有大量评论，你可能不想获取整个树结构。例如，Reddit 有很多评论，只获取前 200 条或默认情况下超过某个阈值的评论，并通过 ajax 获取更多评论。 - Neil McGuigan

哪个Python版本？如果附带的SQLite版本足够新，则支持递归CTE。 - CL.

Answer 1

你的示例是在关系型数据库中表示分层数据的正确方式。你应该使用递归公共表达式（R CTE）来查询表。

过去，你必须使用嵌套集或材料化路径，但 R CTE 是专门为解决它们的缺陷而构建的，因此请使用它。

SQLite 支持递归 CTE，并且它是 SQL:1999 标准的一部分。

以下是一个示例查询：

https://database-patterns.blogspot.com/2014/02/trees-paths-recursive-cte-postgresql.html

- Neil McGuigan

Answer 2

由于数据结构在关系型数据库中没有自然表示，因此根据用例和数据库，有多种存储方式可供选择。

在此演示文稿中的第48幻灯片中，描述了两种方法。

我最喜欢的是材料化路径，它非常简单且功能强大。

希望这可以帮助您。

- Javier

---

为什么在SQLite支持递归CTE的情况下，你会使用材料化路径？ - Neil McGuigan

“数据结构在关系型数据库中没有自然的表示”这种说法是完全错误的。Kevin已经在他的表格中实现了它。关系模型是建立在分层模型基础之上的，它具有所有这些能力（以及更多）。真是太搞笑了。 - PerformanceDBA

@Javier。Karvin已经服用了抑制剂，他对“邻接列表”的描述实际上描述的是层次结构。但他未能理解，要生成“路径”（祖先或后代的连接字符串，或子树报告），只需要一个递归函数，编写起来非常容易。我想如果他能的话，他会这样做。然后他提供了三个极其低效和愚蠢的解决方案，这些方案形成了重复；打破2NF；并需要额外的代码。这不是SQL，而是一些低端的NonSql。 - PerformanceDBA

@Javier。Karvin随后提供了三种极其低效和愚蠢的解决方案，这些方案存在重复；打破2NF；没有完整性；并需要额外的代码。这不是针对SQL的，而是针对一些低端的非关系型数据库。对于他的第77张幻灯片，我只有一行，使用未抑制的“层次结构”代替抑制器的“邻接列表”，并且“查询子树”=死简单。材料化路径是一个花哨的名字，适用于喜欢重复数据；没有完整性；并且不断破坏的额外代码。我称之为“无知”，但当它被支持并提升为“技术”时，我称之为“愚蠢”。 - PerformanceDBA

@PerformanceDBA，你对重复、引用完整性和规范化的观点很有实质性。 - Javier

Answer 3

是的，在关系型数据库中表示树的传统方法就像你在示例中所做的那样：让树中的每个节点都有一个ID和parent_ID。为两者创建索引以便快速检索。

关系型数据库中树的问题不在于创建，而在于高效检索。标准SQL中没有查询可以检索整个树或子树。您需要编写循环来实现。

- Jay

---

@Jay。是的。(1)问题不在于平台是否具有存储过程，而在于平台是否支持递归。低端的伪SQL不支持递归。Oracle（低端）也不行。它在子查询上出现错误。因此，他们必须提供CTE功能，将其移向高端递归。(2)标量使用函数，行使用存储过程。 - PerformanceDBA

@PerformanceDBA Oracle有一个简单的语法来处理递归查询：START AT / CONNECT BY。直到今天我才意识到你可以使用CTE来执行递归查询。很酷。RE（2）我在通用意义上使用了“函数”这个词，表示执行某些工作的代码块。但如果你想要严谨一点，MS SQL也支持“表值函数”和“标量值函数”。 - Jay

@Jay。（1.a）既然您已经同意“loop”是不正确的，请更改您的答案以适应并吸引投票或选择。（1.b）CTE（和Oracle等效物）的唯一目的是在单个语句中执行递归查询（即避免编写过程）。 （2）好的，现在MS有了“表值函数”。我不知道它是用来做什么的，因为自1984年以来，SQL查询就会产生表格化结果。它被称为“子查询”。也许这是为新手准备的，他们不会编写子查询。也许这是我们自1997年以来一直拥有的生成结果集作为表格的功能。 - PerformanceDBA

RE CTE的“唯一目的”：并不反对。我的意图是说，“我没有意识到MS SQL有一个执行递归查询的设施”，而不是“我没有意识到这个特性的许多用途之一是执行递归查询。” - Jay

@Jay。Kevin已经完成了你在第一段要求的所有内容。如果你扩展你的答案，包括这些评论的内容（澄清方法），我会投票支持它。使用递归在“路径”或子树rpt中生成祖先/后代非常容易。 - PerformanceDBA