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.

如果你使用像 Doctrine 这样的 ORM，它包括嵌套集功能.

If you're using an ORM like Doctrine, it includes nested set capabilities.

有些人可能很难掌握 left 和 right 的嵌套集合概念. 我发现使用这些数字作为 open 行号的类比XML 文档中的/close 标签，人们发现它更容易掌握.

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屋！