php/Mysql最佳树形结构 [英] php / Mysql best tree structure
问题描述
我必须构建一棵树，其中将包含约300个节点.该树没有深度限制.因此它可以具有3或15个级别.每个节点可以有无限数量的子代.
I have to build a tree that will contain about 300 nodes inside it. The tree has no depth limitations. So it can have 3 or 15 levels. Each node can have an unlimited number of children.
当务之急是尽快获得完整的树/子树，但有时我也需要添加节点或移动节点.
The priority is to get a complete tree / subtree the faster as possible, but I also need to add nodes or move nodes sometimes but not that often.
我想知道在数据库中存储树的最佳方法以及在php中检索数据的最佳方法.
I want to know the best way to store the tree in the database and the best way to retrieve the data, if possible, in php.
推荐答案
您可以使用嵌套集模型，因为它可以产生非常有效的查询.查看在MySQL中管理分层数据，并阅读名为嵌套集模型.
You can use a Nested Set Model as it yields very efficient queries. Check out Managing Hierarchical Data in MySQL and read the section called Nested Set Model.
If you're using an ORM like Doctrine, it includes nested set capabilities.
对于某些人来说，很难理解 left 和 right的嵌套集合概念.我发现使用这些数字作为open行号的类比. /close标记在XML文档中，人们发现它更容易掌握.
It can be difficult for some to grasp the nested set concepts of left and right. I have found that using those numbers as an analogy for the line numbers of open/close tags in an XML document, folks find it easier to grasp.
例如，以上面的MySQL链接为例:
For instance, take the data example from the MySQL link above:
+-------------+----------------------+-----+-----+
| category_id | name | lft | rgt |
+-------------+----------------------+-----+-----+
| 1 | ELECTRONICS | 1 | 20 |
| 2 | TELEVISIONS | 2 | 9 |
| 3 | TUBE | 3 | 4 |
| 4 | LCD | 5 | 6 |
| 5 | PLASMA | 7 | 8 |
| 6 | PORTABLE ELECTRONICS | 10 | 19 |
| 7 | MP3 PLAYERS | 11 | 14 |
| 8 | FLASH | 12 | 13 |
| 9 | CD PLAYERS | 15 | 16 |
| 10 | 2 WAY RADIOS | 17 | 18 |
+-------------+----------------------+-----+-----+
如果您将 lft ， rgt 字段用作XML文档的行号，则会得到:
If you take the lft, rgt fields and use them as line numbers for an XML document, you get:
1. <electronics>
2. <televisions>
3. <tube>
4. </tube>
5. <lcd>
6. </lcd>
7. <plasma>
8. </plasma>
9. </televisions>
10. <portable electronics>
11. <mp3 players>
12. <flash>
13. </flash>
14. </mp3 players>
15. <cd players>
16. </cd players>
17. <2 way radios>
18. </2 way radios>
19. </portable electronics>
20. </electronics>
以这种方式查看它可以使某些人更容易可视化嵌套集的层次结构.它还使这种方法提高效率的原因更加清楚，因为它可以选择整个节点而无需多个查询或联接.
Seeing it this way can make it much easier for some to visualize the resulting nested set hierarchy. It also makes it clearer why this approach improves efficiency as it makes it possible to select entire nodes without the need for multiple queries or joins.
这篇关于php/Mysql最佳树形结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
