如何在MySQL分层表中选择一个节点的所有父节点？

Question

我有一个MySQL表格，用于表示一棵树形GUI组件的数据，以下是我的表格结构：

treeTable ( 
  id INT NOT NULL PRIMARY KEY, 
  parentId INT, 
  name VARCHAR(255) 
);

parentId 是一个自引用外键。

现在我想编写一个存储过程，它接受一个节点ID并返回一个包含该节点及其所有父节点的结果集。

例如，假设我的表已经填充了这些数据：

1, null, 'root'
2, 1   , 'level_1'
3, 2   , 'level_2'

现在我想获取节点3的所有父节点（节点1和2），并返回一个包含所有树形记录的结果集。有人能帮我吗？

- Ehsan Khodarahmi

---

看看这个主题： MySQL中的分层数据 - user1334169

Answer 1

好问题。在Oracle中，您可以使用类似于CONNECT BY的东西。

由于您正在使用MySQL，建议您更改数据结构以有效地回答该查询。这里提供一些想法。

- Pablo Santa Cruz

Answer 2

MySQL不支持表值函数18.2.1.存储过程语法（这是您需要能够返回任意结果集的内容）。

如果没有它们，您有三个选择：

1. 将树查询展开到固定的最大深度并限制您的层次结构中允许的嵌套，
2. 使用循环将数据写入临时表并引入一些约定以将结果返回给调用方。您需要考虑可重入性，或者
3. 通过在支持表中包含每个组件的所有祖先来预先计算结果（如所示），并使用树表上的触发器进行维护。这样，存储过程以适当的方式过滤父表中的行返回。您需要创建一个复合主键，可能还需要为高效访问创建索引。

第三个选项具有非常小的行，性能良好，并避免了人为限制。

parentTable (
    id INT NOT NULL,
    parentId INT NOT NULL
); 

在这个应用程序中，建议使用嵌套集方法可能是合适的，因为数据大部分是静态的。如果数据集快速变化，那么平均每次插入或删除表中一半的行将开始影响I/O性能。

- Pekka

Answer 3

这里有一篇类似的讨论，可能有助于解决这个问题。

我认为我可以通过递归检索数据来解决这个问题，直到达到根节点（父节点为空）。最初我可能会倾向于在存储过程之外完成此操作（重复调用该过程，直到检索到的行具有空父项），但是在我在这里引用的网页上提供的“闭包表”解决方案看起来是一个绝佳的解决方案。

- itsmatt

Answer 4

在“检索单个路径”下面这里看一下。但最好使用嵌套集的方法，这样处理树形结构会更容易。我还建议阅读“数据库中的树形结构-高级数据结构”演示文稿。

- Sergei

Answer 5

还有物化路径需要考虑。这是一个非常简单的概念，与数据库无关。与嵌套集相比，更容易管理插入等操作，因为您不必在插入之前知道左/右节点等信息。

- dhoss