基于邻接表示的动态路径枚举的预序截线

嵌套集来自:

Konchog https://stackoverflow.com/a/42781302/895245 约翰尼·布坎南https://stackoverflow.com/a/194031/895245

是我见过的唯一有效的遍历方式，但代价是更新速度较慢。这可能是大多数人想要预订的。

来自https://stackoverflow.com/a/192462/895245的闭包表很有趣，但我不知道如何强制提前:MySQL闭包表分层数据库-如何以正确的顺序拉出信息

主要是为了好玩，这里有一个递归计算1.3.2.5的方法。前缀，并在最后根据它们进行排序，仅基于父ID/子索引表示。

好处:

更新只需要更新每个兄弟节点的索引

缺点:

N ^2内存使用量对于超深树来说是最坏的情况。这可能是相当严重的，这就是为什么我说这种方法可能主要只是为了好玩。但也许在某些超高更新的情况下，有人会想要使用它?谁知道 递归查询，所以读的效率比嵌套集要低

创建并填充表:

CREATE TABLE "ParentIndexTree" (
  "id" INTEGER PRIMARY KEY,
  "parentId" INTEGER,
  "childIndex" INTEGER NOT NULL,
  "value" INTEGER NOT NULL,
  "name" TEXT NOT NULL,
  FOREIGN KEY ("parentId") REFERENCES "ParentIndexTree"(id)
)
;
INSERT INTO "ParentIndexTree" VALUES
  (0, NULL, 0, 1, 'one'  ),
  (1, 0,    0, 2, 'two'  ),
  (2, 0,    1, 3, 'three'),
  (3, 1,    0, 4, 'four' ),
  (4, 1,    1, 5, 'five' )
;

代表树:

    1
   / \
  2   3
 / \
4   5

然后，对于像PostgreSQL这样的数组DBMS (https://www.postgresql.org/docs/14/arrays.html):

WITH RECURSIVE "TreeSearch" (
  "id",
  "parentId",
  "childIndex",
  "value",
  "name",
  "prefix"
) AS (
  SELECT
    "id",
    "parentId",
    "childIndex",
    "value",
    "name",
    array[0]
  FROM "ParentIndexTree"
  WHERE "parentId" IS NULL

  UNION ALL

  SELECT
    "child"."id",
    "child"."parentId",
    "child"."childIndex",
    "child"."value",
    "child"."name",
    array_append("parent"."prefix", "child"."childIndex")
  FROM "ParentIndexTree" AS "child"
  JOIN "TreeSearch" AS "parent"
    ON "child"."parentId" = "parent"."id"
)
SELECT * FROM "TreeSearch"
ORDER BY "prefix"
;

这将创建动态的表单前缀:

1 -> 0
2 -> 0, 0
3 -> 0, 1
4 -> 0, 0, 0
5 -> 0, 0, 1

然后PostgreSQL按字母顺序排序:

1 -> 0
2 -> 0, 0
4 -> 0, 0, 0
5 -> 0, 0, 1
3 -> 0, 1

这就是我们想要的预购结果。

对于像SQLite这样没有数组的DBMS，可以通过使用固定宽度的整数字符串来编码前缀。二进制是理想的，但我不知道怎么做，所以十六进制可以工作。当然，这意味着你必须选择一个最大深度，以适应所选字节的数量，例如下面我选择6，允许每个节点最多16^6个子节点。

WITH RECURSIVE "TreeSearch" (
  "id",
  "parentId",
  "childIndex",
  "value",
  "name",
  "prefix"
) AS (
  SELECT
    "id",
    "parentId",
    "childIndex",
    "value",
    "name",
    '000000'
  FROM "ParentIndexTree"
  WHERE "parentId" IS NULL

  UNION ALL

  SELECT
    "child"."id",
    "child"."parentId",
    "child"."childIndex",
    "child"."value",
    "child"."name",
    "parent"."prefix" || printf('%06x', "child"."childIndex")
  FROM "ParentIndexTree" AS "child"
  JOIN "TreeSearch" AS "parent"
    ON "child"."parentId" = "parent"."id"
)
SELECT * FROM "TreeSearch"
ORDER BY "prefix"
;

一些嵌套的集合注释

在看了其他嵌套的答案后，这里有几个点让我有点困惑。

Jonny Buchanan展示了他的嵌套设置:

__________________________________________________________________________
|  Root 1                                                                  |
|   ________________________________    ________________________________   |
|  |  Child 1.1                     |  |  Child 1.2                     |  |
|  |   ___________    ___________   |  |   ___________    ___________   |  |
|  |  |  C 1.1.1  |  |  C 1.1.2  |  |  |  |  C 1.2.1  |  |  C 1.2.2  |  |  |
1  2  3___________4  5___________6  7  8  9___________10 11__________12 13 14
|  |________________________________|  |________________________________|  |
|__________________________________________________________________________|

这让我想知道为什么不使用更简单的外观:

__________________________________________________________________________
|  Root 1                                                                 |
|   ________________________________    _______________________________   |
|  |  Child 1.1                     |  |  Child 1.2                    |  |
|  |   ___________    ___________   |  |   ___________   ___________   |  |
|  |  |  C 1.1.1  |  |  C 1.1.2  |  |  |  |  C 1.2.1  | |  C 1.2.2  |  |  |
1  2  3___________|  4___________|  |  5  6___________| 7___________|  |  | 
|  |________________________________|  |_______________________________|  |
|_________________________________________________________________________|

每个端点都没有额外的数字。

但当我真正尝试实现它时，我注意到很难/不可能实现这样的更新查询，除非我有Konchog所使用的父级信息。问题是，当树被移动时，在某种情况下很难/不可能区分兄弟姐妹和父母，我需要这来决定是否要在缩小差距时减少右手边。

左/大小vs左/右:你可以在数据库中以任何一种方式存储它，但我认为左/右可以更有效，因为你可以用多列索引(左，右)索引DB，然后可以用来加速祖先查询，这是类型:

left < curLeft AND right > curLeft

在Ubuntu 22.04, PostgreSQL 14.5, SQLite 3.34.0上测试。

要扩展Bill的SQL解决方案，基本上可以使用平面数组来实现相同的功能。此外，如果你的字符串都有相同的长度，你的最大子代数是已知的(比如在一个二叉树中)，你可以使用一个单一的字符串(字符数组)。如果你有任意数量的孩子，事情就会变得复杂一些……我必须检查我的旧笔记，看看能做些什么。

然后，牺牲一点内存，特别是如果你的树是稀疏的和/或不平衡的，你可以，通过一些索引数学，通过存储你的树随机访问所有的字符串，宽度优先在数组中，就像这样(对于二叉树):

String[] nodeArray = [L0root, L1child1, L1child2, L2Child1, L2Child2, L2Child3, L2Child4] ...

你知道弦的长度，你知道 我现在在工作，所以不能花太多时间在上面，但有兴趣，我可以获取一些代码来做到这一点。 我们过去用它来搜索由DNA密码子组成的二叉树，一个构建树的过程，然后我们将其平铺以搜索文本模式，当找到时，尽管索引数学(从上面反向)，我们将节点找回…非常快速和有效，我们的树很少有空节点，但我们可以在一瞬间搜索千兆字节的数据。

假设你知道根元素是0，下面是输出到文本的伪代码:

function PrintLevel (int curr, int level)
    //print the indents
    for (i=1; i<=level; i++)
        print a tab
    print curr \n;
    for each child in the table with a parent of curr
        PrintLevel (child, level+1)


for each elementID where the parentid is zero
    PrintLevel(elementID, 0)

如果有选择的话，我会用对象。我将为每条记录创建一个对象，其中每个对象都有一个子集合，并将它们全部存储在一个assoc数组(/hashtable)中，其中Id是键。并在集合中闪动一次，将子元素添加到相关的子字段中。简单。

但是因为你限制一些好的OOP的使用是没有乐趣的，我可能会基于:

function PrintLine(int pID, int level)
    foreach record where ParentID == pID
        print level*tabs + record-data
        PrintLine(record.ID, level + 1)

PrintLine(0, 0)

编辑:这与其他几个条目类似，但我认为它稍微干净一些。我要补充一点:这是非常sql密集的。这是令人讨厌的。如果可以选择的话，选择面向对象的方法。

现在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中的递归查询。

以下是我2008年的原始答案:

---

有几种方法可以在关系数据库中存储树形结构的数据。您在示例中展示的内容使用了两个方法:

邻接表(“父”列)和 路径枚举(名称列中的虚线数字)。

另一种解决方案称为嵌套集，它也可以存储在同一个表中。阅读Joe Celko的“Smarties SQL中的树和层次结构”，了解更多关于这些设计的信息。

我通常更喜欢一种称为闭包表(又名“邻接关系”)的设计来存储树状结构的数据。它需要另一个表，但是查询树非常简单。

我在演示中用SQL和PHP构建分层数据模型时提到了闭包表，在我的书《SQL反模式第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向下延伸。

---

回复:e-satis关于直系子女(或直系父母)的评论。您可以向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);

然后，您可以在搜索中添加一个术语，以查询给定节点的直接子节点。这些是path_length为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的所有后代节点，将它们连接到包含其他节点属性(如名称)的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         |
+------------+-------------+

---

一位用户建议今天进行编辑。版主批准了编辑，但我要撤销它。

编辑建议上面最后一个查询中的ORDER BY应该是ORDER BY b.path_length, f.name，大概是为了确保顺序与层次结构匹配。但这行不通，因为它会将“Node 1.1.1”排在“Node 1.2”之后。

如果您希望排序以合理的方式匹配层次结构，这是可能的，但不是简单地按路径长度排序。例如，请参阅我对MySQL闭包表分层数据库的回答-如何以正确的顺序提取信息。

如果可以创建嵌套的哈希映射或数组，那么我只需从表的开头往下走，并将每个项添加到嵌套数组中。我必须跟踪每一行到根节点，以便知道要插入到嵌套数组中的哪一层。我可以使用记忆，这样我就不需要一遍又一遍地查找相同的父节点。

编辑:我会先将整个表读入一个数组，这样它就不会重复查询DB。当然，如果您的表非常大，这是不实际的。

在构建结构之后，我必须先对其进行深度遍历，并打印出HTML。

没有更好的基本方法来存储这些信息使用一个表(我可能是错的;)，并希望看到一个更好的解决方案)。然而，如果您创建了一个使用动态创建的db表的方案，那么您以牺牲简单性和SQL地狱的风险为代价打开了一个全新的世界;)。