mysql怎么建立层次结构_使用 MySQL 管理层次结构的数据

3邻接表(Adjacency List)模型

由于使用纯 SQL 处理邻接表模型存在种种不便，因此 Mike Hillyer 郑重的介绍了嵌套集(Nested Set)模型。当使用这种模型时，我们把层次结构的节点和路径从脑海中抹去，把它们想象为一个个容器：

可以看到层次关系没有改变，大的容器包含子容器。我们使用容器的左值和右值来建立数据表：

需要注意left和right是 MySQL 的保留字，因此使用标识分隔符来标记它们。

插入数据：

查看内容：

可以看到：

我们是如何确定左编号和右编号的呢，通过下图我们可以直观的发现只要会数数即可完成：

回到树形模型该怎么处理，通过下图，对数据结构稍有概念的人都会知道，稍加改动的先序遍历算法即可完成这项编号的工作：

  获取整棵树

一个节点的左编号总是介于其父节点的左右编号之间，利用这个特性使用 self-join 链接到父节点，可以获取整棵树：

结果如下：

但是这样我们丢失了层次的信息。怎么办呢？使用COUNT()函数和GROUP BY子句，可以实现这个目的：

需要注意 MySQL 5.7.5 开始默认启用了only_full_group_by模式，让GROUP BY的行为与 SQL92 标准一致，因此直接使用ORDER BY node.`left`会产生错误：

使用ANY_VALUE() 是避免这个问题的一种的途径。

结果如下：

稍作调整就可以直接显示层次：

结果相当漂亮：

当然客户端代码可能会更倾向于使用depth值，对返回的结果集进行循环，Web 开发人员可以根据其增大或减小使用

/或

• /

等。

   获取节点在子树中的深度

要获取节点在子树中的深度，我们需要第三个 self-join 以及子查询来将结果限制在特定的子树中以及进行必要的计算：

结果是：

   寻找一个节点的直接子节点

使用邻接表模型时这相当简单。使用嵌套集时，我们可以在上面获取子树各节点深度的基础上增加一个HAVING子句来实现：

结果：

   获取所有叶节点

观察带编号的嵌套模型，叶节点的判断相当简单，右编号恰好比左编号多 1 的节点就是叶节点：

结果如下：

  获取单个节点的完整路径

仍然是使用 self-join 技巧，不过现在无需顾虑节点的深度了：

结果如下：

   聚集操作

我们添加一张releases表，来展示一下在嵌套集模型下的聚集(aggregate)操作：

加入一些数据，假设这些数据是指某个软件支持的发行版：

那么，下面的查询可以知道每个节点下涉及的发布版数量，如果这是一个软件支持的发布版清单，或许测试人员想要知道他们得准备多少种虚拟机吧：

结果如下：

如果层次结构是一个分类目录，这个技巧可以用于查询各个类别下有多少关联的商品。

   添加节点

再次回顾这张图：

如果我们要在Gentoo之后增加一个Slackware，这个新节点的左右编号分别是 10 和 11，而原来从 10 开始的所有编号都需要加 2。我们可以：

使用之前掌握的技巧看一下现在的情况：

结果为：

如果新增的节点的父节点原来是叶节点，我们需要稍微调整一下之前的代码。例如，我们要新增Slax作为Slackware的子节点：

现在，数据形如：

   删除节点

删除节点的操作与添加操作相对，当要删除一个叶节点时，移除该节点并将所有比该节点右编码大的编码减 2。这个思路可以扩展到删除一个节点及其所有子节点的情况，删除左编码介于节点左右编号之间的所有节点，将右侧的节点编号全部左移该节点原编号宽度即可：

可以看到Slackware子树被删除了：

稍加调整，如果对介于要删除节点左右编号直接的节点对应编号左移 1，右侧节点对应编号左移 2，则可以实现删除一个节点，其子节点提升一层的效果，例如我们尝试删除 RHEL但保留它的子节点：

结果为：