递归MySQL查询? [英] Recursive MySQL query?
问题描述
我有一组按层次结构组织的数据，应该可以增长到任意大小.我需要检索整棵树，但是我无法弄清楚如何仅使用SQL来做到这一点.我当前的解决方案是创建一个临时表，并使用递归函数依次查询树的分支，然后将结果存储在临时表中，随后我再次对其进行查询以产生所需的结果.
我的问题是，实际上我在做什么是联接可以纠正的内容?构造一个中间表，然后查询结果.似乎应该有一种使用联接的方法，但是MySQL文档仅涵盖了检索树的有限深度的部分.有没有办法做到这一点?我正在用PHP做到这一点.
MySQL不支持递归查询.
我建议您看看Bill Karwin的演示文稿，其中比较了用于存储分层数据的四种不同模型，并探讨了它们的优缺点:
- 邻接表
- 路径枚举
- 嵌套集
- 关闭表
或者，如果只想选择整个树(如表中的所有数据一样)，则可以使用简单的查询SELECT * FROM yourtable并在客户端中重建树结构.
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.
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 doesn't support recursive queries.
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:
- Adjacency list
- Path enumeration
- Nested sets
- Closure table
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.
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屋！
