递归 MySQL 查询? [英] Recursive MySQL query?

问题描述

我有一组按层次组织的数据，应该能够增长到任意大小.我需要检索整个树，但我不知道如何只用 SQL 来完成.我目前的解决方案是创建一个临时表并使用递归函数连续查询树的分支，然后将结果存储在临时表中，我随后再次查询以产生我想要的结果.

I have a set of data that's organized hierarchically that should be able to grow to an arbitrary size. I need to retrieve the entire tree, but I can't figure out how to do it with just SQL. My current solution is to create a temporary table and use a recursive function to successively query branches of the tree and then store the result in the temporary table which I subsequently query again to produce my desired result.

我的问题是，我所做的本质上是连接正确的操作?构建一个中间表，然后查询结果.似乎应该有一种方法可以使用连接来做到这一点，但 MySQL 文档仅涵盖检索树的部分直到有限深度.有没有办法做到这一点?我是用 PHP 做的.

My question is, what I'm doing is essentially what a join does correct? Constructing an intermediate table and then querying over the results. It seems like there should be a way to do it with joins, but the MySQL documentation only covers retrieving parts of a tree up to finite depth. Is there a way to do this? I'm doing this in PHP.

推荐答案

MySQL 不支持递归查询.

MySQL doesn't support recursive queries.

我建议您查看 Bill Karwin 的演示文稿，他在那里比较了用于存储分层数据的四种不同模型并查看了它们的优缺点:

I would suggest that you look at Bill Karwin's presentation where he compares four different models for storing heirarchical data and looks at their pros and cons:

• 邻接表
• 路径枚举
• 嵌套集
• 收尾表

幻灯片 48 显示了每种模型的某些类型查询的相对难度.从您的问题来看，您似乎对查询子树"最感兴趣，邻接列表(您当前使用的模型)在这四个子树中的表现最差.

Slide 48 shows the relative difficulty of certain types of queries with each of the models. From your question it sounds like you are interested most in "Query subtree", for which the adjacency list (the model you are currently using) performs the most poorly of the four.

或者，如果您只想选择整个树，就像在表中的所有数据中一样，那么您可以使用简单查询 SELECT * FROM yourtable 并在客户端重建树结构.

Alternatively if you just want to select the entire tree, as in all data in the table, then you could use the simple query SELECT * FROM yourtable and reconstruct the tree structure in the client.

这篇关于递归 MySQL 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！