MySQL中是否有"connect by"替代方案? [英] Is there a `connect by` alternative in MySQL?
问题描述
如果我使用Oracle，则可以使用connect by关键字来创建分层查询.目前，我正在一个项目上使用MySQL，我想知道MySQL中是否还有connect by的替代方案?
If I use Oracle there is the connect by keyword which can be used to create hierarchical queries. Currently I'm using MySQL on a project and I would like to know whether there is an alternative for connect by in MySQL?
我尝试了google，但到目前为止没有任何帮助.我想要实现的是通过一个查询从数据库中获取一棵树.涉及两个表:
I tried google but for no avail so far. What I'm trying to achieve is to fetch a tree from the database with one query. There are two tables involved:
areas和area_to_parent_join.后者包含两个ID，一个是area_id，另一个是parent_id.因此，它基本上是一个自联接，我可以使用该模型创建图.实际上，它目前仅用于创建树，但是将来可能会改变.但无论哪种情况，我想要的只是一棵生成树.
areas and area_to_parent_join. The latter contains two ids one is area_id and the other is parent_id. So it is basically a self-join and I can create graphs using that model. In fact it is currently only used to create trees but this might change in the future. But in either case what I would like to have is just a spanning tree.
areas可能有超过1.000.000条记录，这使得大多数空间密集型选项不可行.
areas might have more than 1.000.000 records which makes most of the space-intensive options unfeasible.
推荐答案
如评论中所述，使用mysql并非没有捷径.
As said in comments, there isn't a short way with mysql.
但是！
如果可以更改数据库结构，则可以部署更好的设计来处理树状层次结构.
If you have the possibility to change the database structure, you can deploy a better design to handle tree-like hierarchies.
如果您遵循 此教程 来自Bill Karwin( HERE 是引用幻灯片演示教程的原始答案)，您可以找到用于建模层次结构的4种方法:
If you follow THIS TUTORIAL from Bill Karwin (HERE is the original answer which references that slideshow tutorial), you can find 4 methods used to model an hierarchical structure:
- 友情清单
- 路径枚举
- 嵌套集
- 关闭表
现在，最好的模型是第四个模型(我将其他三个模型的描述留给读者)，它基本上需要2个表:一个用于元素，一个用于路径.在路径表(闭包表本身)中，您将存储从每个节点到每个后代的所有路径(而不仅仅是直接的子代！).
Now, the best model possible is the 4th one (I leave descriptions of the other 3 models to the reader), which basically needs 2 tables: one for the elements and one for the paths. In the paths table (the closure table itself), you'll store every path from each node to every descendant (not just the direct childs!).
建议还保存每行的路径长度，因为这样可以更轻松地查询树中的直接子代.
It's suggested to save also the path length for each row, because it makes easier to query for immediate childrens in the tree.
即使此解决方案需要更多空间，它也具有最佳的整体性能，并且非常易于使用:它完全不依赖递归查询，并且将为整个数据集提供参照完整性！
Even if this solution requires more space, it has the best overall performance and it's really easy to use: it doesn't rely on recursive queries at all AND it will grants referential integrity for the whole dataset!
例如，获取节点#4的每个子节点:
For example, to get every child of the node #4:
select a.*
from nodes a
join paths b
on a.node_id = b.descendant
where b.ancestor = 4
另一个例子:获取节点#11的所有祖先
Another example: get all the ancestors of the node #11
select a.*
from nodes a
join paths b
on a.node_id = b.ancestor
where b.descendant = 11
需要删除节点#6的子树
need to delete the subtree of the node #6
delete from paths where descendant in
(select descendant from paths where ancestor = 6)
这篇关于MySQL中是否有"connect by"替代方案?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
