选择一个由mysql中的邻接表模型表示的任意深度的树？
在 mysql 中，我有一棵使用邻接列表模型表示的树。
MYTREE
id
parent_id
title
我想知道：
给定一个节点的 id，有没有办法选择该节点下面的整个树，并包含深度信息？这棵树的深度是任意的，所以我无法说出可能有多少层。但结果集可能看起来像这样：
ID TITLE DEPTH
4 title1 1
8 title2 2
16 title8 3
9 title3 2
15 title4 3
我知道可以使用嵌套集模型来做到这一点。但嵌套集有些事情并不理想，我希望不必切换。
谢谢你的建议！
In mysql, I have a tree that is represented using the adjacency list model.
MYTREE
id
parent_id
title
I am wondering:
Given the id of a node, is there any way to SELECT the entire tree beneathe that node, complete with depth information? The tree is arbitrarily deep, so I cannot say how many levels there may be. But the resultset might look something like this:
ID TITLE DEPTH
4 title1 1
8 title2 2
16 title8 3
9 title3 2
15 title4 3
I know that it is possible to do this using the nested sets model. But there are things about nested sets that are not ideal, and I'm hoping not to have to switch over.
Thanks for the advice!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
编辑：我没有注意到标题中的任意深度子句：
您可以编写一个存储过程来递归行。
但是嵌套集有什么不理想的地方（至少插入之前触发器或存储过程无法处理这一点）？
EDIT: I didn't notice the arbitrarily deep clause in the title:
You could write a Stored Procedure to recurse over the rows.
But what's not ideal about Nested Sets (at least that couldn't be taken care of by a before insert trigger or a stored procedure)?
简短的回答：不。
遍历用父指针表示的树的唯一方法是在一组父 ID 之后跟随一组父 ID。如果您可以通过简单地在表中连接多次来限制深度，那么这是可能的，但对于无限深度，应用程序端循环通常是可行的方法。
Short answer: no.
The only way to traverse a tree represented with parent pointers is to follow one set of parent IDs after the next. That's possible if you can limit the depth, by simply joining that many times across the table, but with an unlimited depth an application-side loop is generally the way to go.