MySQL 在关系数据库中存储树结构的已知方法有哪些？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3362669/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What are the known ways to store a tree structure in a relational DB?
提问by Itay Moav -Malimovka
There is the "put a FK to your parent" method, i.e. each records points to it's parent.
Which is a hard for read actions but very easy to maintain.
有“把 FK 给你的父级”方法，即每条记录都指向它的父级。
这对于读取操作来说很难，但很容易维护。
And then there is a "directory structure key" method:
然后还有一个“目录结构键”方法：
0001.0000.0000.0000 main branch 1
0001.0001.0000.0000 child of main branch one
etc
Which is super easy to read, but hard to maintain.
What are the other ways and their cons/pros?
这是超级容易阅读，但很难维护。
其他方式及其优缺点是什么？
回答by Baju
As always: there is no best solution. Each solution makes different things easier or harder. The right solution for you depends on which operation you will do most.
一如既往：没有最佳解决方案。每个解决方案都会使不同的事情变得更容易或更难。适合您的解决方案取决于您最常执行的操作。
Naive Approach with parent-id:
带有父 ID 的天真方法：
Pros:
优点：
easy to implement
easy to move a big subtree to another parent
insert is cheap
Needed Fields directly accessible in SQL
易于实施
很容易将一个大的子树移动到另一个父级
插入很便宜
可在 SQL 中直接访问的所需字段
Cons:
缺点：
retrieving a whole tree is recursive and therefore expensive
finding all parents is expensive too ( SQL doesn't know recursions... )
检索整棵树是递归的，因此很昂贵
找到所有的父母也很昂贵（SQL 不知道递归......）
Modified Preorder Tree Traversal ( saving a start- & end-point) :
修改的预序树遍历（保存起点和终点）：
Pros:
优点：
Retrieving a whole tree is easy and cheap
Finding all parents is cheap
Needed Fields directly accessible in SQL
Bonus: you're saving the order of childnodes within its parentnode too
取回整棵树既简单又便宜
找到所有的父母很便宜
可在 SQL 中直接访问的所需字段
奖励：您也在其父节点中保存子节点的顺序
Cons:
缺点：
- Inserting / Updating can be very expensive, as you'll maybe have to update a lot of nodes
- 插入/更新可能非常昂贵，因为您可能需要更新很多节点
Saving a path in each Node:
在每个节点中保存路径：
Pros:
优点：
Finding all parents is cheap
Retrieving a whole tree is cheap
Inserting is cheap
找到所有的父母很便宜
取回整棵树很便宜
插入很便宜
Cons:
缺点：
Moving a whole tree is expensive
Depending on the way you save the path, you won't be able to work with it directly in SQL, so you'll always need to fetch & parse it, if you want to change it.
移动整棵树很昂贵
根据您保存路径的方式，您将无法直接在 SQL 中使用它，因此如果您想更改它，您将始终需要获取并解析它。
I'd prefer one of the last two, depending on how often the data changes.
我更喜欢最后两个中的一个，具体取决于数据更改的频率。
回答by TRiG
Modified Preorder Tree Traversal
修改前序树遍历
This is a method which uses a non-recursive function (usually a single line of SQL) to retrieve trees from the database, at the cost of being a little trickier to update.
这是一种使用非递归函数（通常是单行 SQL）从数据库中检索树的方法，代价是更新有点棘手。
**
**
Section 2 of the Sitepoint article Storing Hierarchical Data in a Databasefor more detail.
有关更多详细信息，请参阅 Sitepoint 文章在数据库中存储分层数据的第 2 部分。
回答by Borealid
I'd say the "golden way" to store a hierarchical data structure is to use a hierarchical database. Such as, for instance, HDB. That's a relational database that handles trees quite well. If you want something more powerful, LDAP might do for you.
我会说存储分层数据结构的“黄金方式”是使用分层数据库。例如，HDB。这是一个可以很好地处理树的关系数据库。如果您想要更强大的功能，LDAP 可能适合您。
A SQL database is ill-suited to this abstract topology.
SQL 数据库不适合这种抽象拓扑。
回答by DarthVader
I don't think it's hard to build a tree structure with a relational database.
我认为用关系数据库构建树结构并不难。
However, an object oriented database would work much better for this purpose.
然而，面向对象的数据库将为此目的工作得更好。
Using an object oriented database:
使用面向对象的数据库：
parent has a set of child1
child1 has a set of child2
child2 has a set of child3
...
...
In an object oriented database you can build this structure pretty easily.
在面向对象的数据库中，您可以非常轻松地构建此结构。
In a relational database, you will have to maintain foreign keys to parent.
在关系数据库中，您必须维护父级的外键。
parent
id
name
child1
parent_fk
id
name
child2
parent_fk
id
name
..
Essentially, while you are building your tree structure you will have to join all these tables, or you can iterate through them.
本质上，当您构建树结构时，您必须连接所有这些表，或者您可以遍历它们。
foreach(parent in parents){
foreach(child1 in parent.child1s)
foreach(child2 in child1.child2s)
...
