MySQL分层数据的邻接表模型与嵌套集模型? [英] Adjacency List Model vs Nested Set Model for MySQL hierarchical data?

问题描述

有在MySQL中使用层次结构数据的两种方法:

1. 邻接表模型
2. 嵌套集模型

邻接表模型的一个主要问题是，我们需要为每个节点运行一个查询以获取层次结构的路径.

A major problem of the Adjacency List Model is that we need to run one query for each node to get the path of the hierarchy.

在嵌套集模型中不存在此问题，但是对于每个添加的节点，必须对所有其他 left 和 right 值.

In the Nested Set Model this problem does not exist, but for each added node is necessary to give a MySQL UPDATE on all others left and right value.

我的分层数据不是静态数据，例如电子商务的产品类别.是按等级顺序不断注册用户.

My hierarchical data is not static data, such as product categories of e-commerce. Are constant registration of users in hierarchical sequence.

在我的应用程序中，尽管有许多常量用户注册，但我还需要获取层次结构路径，直到到达层次结构中的第一个节点为止.

In my application, while there are many constants users registration, I also need to get the hierarchical path until reach the first node in the hierarchy.

分析我的情况，这两种选择中哪一种最适合我的应用?

Analyzing my situation, which of the two alternatives would be best for my application?

推荐答案

嵌套集模型如今在数据库中并不常用，因为它比Adiacency List模型更复杂，因为它需要管理两个指针" "，而不是一个.实际上，当复杂或不可能进行遍历层次结构的递归查询时，已在数据库中引入了嵌套集模型.

The Nested Set Model is nowdays not commonly used in databases, since it is more complex than the Adiacency List Model, given the fact that it requires managing two "pointers" instead of a single one. Actually, the Nested Set Model has been introduced in databases when it was complex or impossible to do recursive queries that traversed a hierarchy.

从1999年开始，标准SQL包括所谓的递归公用表表达式或递归CTE，它使进行查询的遍历具有 any 编号的层次结构中的递归路径的查询变得更加简单(和标准化！).级别.

From 1999, standard SQL include the so called Recursive Common Table Expressions, or Recursive CTE, which makes more simple (and standardized!) to make queries that traverse recursive path within a hierarchy with any number of levels.

所有主要的DBMS系统现在都包含此功能，但有一个明显的例外:MySQL.但是在MySQL中，您可以使用存储过程来克服此问题.例如，参见 StackOverflow上的这篇文章或这篇文章放在dba.stackexchange .

All the major DBMS systems have now included this feature, with a notably exception: MySQL. But in MySQL you can overcome this problem with the use of stored procedures. See, for instance, this post on StackOverflow, or this post on dba.stackexchange.

因此，总而言之，这些是我的建议:

So, in summary, these are my advices:

1. 如果您仍然可以决定使用哪种DBMS，请强烈考虑其他选择:例如，如果您要坚持使用开源数据库，请使用

1. If you can still decide which DBMS use, consider strongly some alternatives: for instance, if you want to stick with an open source database, use PostgreSQL, use the Adiacency List Model, and go with Recursive CTEs for your queries.
2. If you cannot change the DBMS, still you should go with the Adiacency List Model, and use stored procedures as those cited in the references.

更新

这种情况随着MySQL 8的发展而改变，它正在开发中，并且

This situation is changing with MySQL 8, which is currently in developement and which will integrate Recursive CTEs, so that from that version the Adiacency List Model will be more simple to use.

这篇关于MySQL分层数据的邻接表模型与嵌套集模型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！