如何在分层mysql表中选择节点的所有父节点？
我有一个 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）并返回包含所有树记录的结果集。有人可以帮我吗？
I have a MySQL table that represents data for a tree GUI component, here's the structure of my table:
treeTable (
id INT NOT NULL PRIMARY KEY,
parentId INT,
name VARCHAR(255)
);
parentId is a self-referencing foreign key.
Now I want to write a stored procedure which gets a node id and returns a result set that contains that node and all of its parents.
For example, suppose that my table has filled with this data:
1, null, 'root'
2, 1 , 'level_1'
3, 2 , 'level_2'
Now I want to get all parent nodes of node 3 (nodes 1 and 2) and return a result set that contains all tree records. Can anybody help me please?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
好问题。在 Oracle 中，您可以使用诸如 CONNECT BY 之类的东西。
由于您使用的是 MySQL，我建议您更改数据结构以有效地回答该查询。 这里有一些想法。
Good question. In Oracle you would use something like CONNECT BY.
Since you are using MySQL, I would suggest you change your data structure to efficiently answer that query. Here are some ideas.
有一个类似的对此进行讨论可能有助于解决此问题。
我想我可以通过递归检索数据直到到达根节点（父节点为空）来解决这个问题。我最初可能倾向于在存储过程之外执行此操作（重复调用该事物，直到检索到的行具有空父级），但我在此处引用的页面上的“闭包表”解决方案看起来是一个很好的解决方案。
There was a similar discussion to this that might be helpful in solving this problem.
I think I might attack this problem by recursively retrieving the data until I'd reached the root node (parent was null). I might have been inclined to do this outside of the stored procedure initially (repeatedly calling the thing until the retrieved row had the null parent), but the "closure table" solution on the page I referenced here looks like a great solution.
请参阅此处“检索单个路径”下的内容。但最好使用嵌套集方法，使用树会更容易。我还建议阅读“数据库中的树 - 高级数据结构”演示。
Look here under "Retrieving a Single Path". But better use a nested set approach, it will be much easier to work with a tree. Also I recommend reading "Trees In The Database - Advanced data structures" presentation.
还有具体化路径需要考虑。非常简单的概念，实际上与数据库无关。与嵌套集相比，管理插入等要容易得多，在插入之前您不必知道您是左/右节点等。
There's also materialized paths to think about. Pretty simple concept that's really database agnostic. Much easier to manage inserts etc as in contrast to nested sets, you don't have to know you're left/right nodes etc before you insert.
MySQL 不支持表值函数 18.2.1。存储例程语法（这是您需要能够返回任意结果集的语法）。
如果没有它们，您将有三种选择：
第三个选项具有非常小的行，将提供良好的性能并避免人为限制。
使用嵌套集方法的建议可能适合此数据大部分是静态的应用程序。快速变化的数据集将开始影响 I/O 性能，因为每次插入或删除平均会更新表中一半的行。
MySQL does not support table valued functions 18.2.1. Stored Routine Syntax (which is what you need to be able to return an arbitrary result set).
Without them you have three choices:
The third option has very small rows, will give good performance and avoids artificial limitations.
The suggestion to use a nested set approach may be appropriate in this application where the data is largely static. A rapidly changing dataset would start to impact on I/O performance as on average half of the rows in the table are updated for each insert or delete.