树状结构数据的数据库表设计及使用 - 3. 闭包表（Closure Table）模型

最新推荐文章于 2024-05-22 09:34:48 发布

菠萝y

最新推荐文章于 2024-05-22 09:34:48 发布

阅读量6.1k

点赞数 2

分类专栏： mysql 数据结构与算法理论学习文章标签：数据库 java sql

原文链接：https://www.modb.pro/db/378605

版权

数据结构与算法同时被 3 个专栏收录

31 篇文章 1 订阅

订阅专栏

理论学习

26 篇文章 1 订阅

订阅专栏

mysql

23 篇文章 3 订阅

订阅专栏

本文详细介绍了如何在MySQL中使用闭包表模型存储和操作树形结构数据，包括创建表、存储过程、插入数据、查询子树和叶节点等操作，并展示了从邻接表转换为闭包表的过程。闭包表模型通过牺牲存储空间换取查询效率，适用于需要快速遍历树结构的场景。

摘要由CSDN通过智能技术生成

本文以 MySQL 为例。文档比较长，故分为5部分发出：

📝 邻接表（Adjacency List）模型

🎨 路径枚举（Path Enumeration）模型

🧑‍💻 闭包表（Closure Table）模型

🤹 嵌套集（Nested Set）模型

🧮 性能比较与分析

闭包表模型

闭包表（Closure Table）是一种通过空间换时间的模型，它是用一个专门的关系表（其实这也是我们推荐的归一化方式）来记录树上节点之间的层级关系以及距离。

CREATE TABLE `NodeInfo` (

  `node_id` INT NOT NULL AUTO_INCREMENT,

  `node_name` VARCHAR (255),

  PRIMARY KEY (`node_id`)

) DEFAULT CHARSET = utf8mb4;



CREATE TABLE `NodeRelation` (

  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID',

  `ancestor` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '祖先节点',

  `descendant` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '后代节点',

  `distance` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '相隔层级，>=1',

  PRIMARY KEY (`id`),

  UNIQUE KEY `uniq_anc_desc` (`ancestor`,`descendant`),

  KEY `idx_desc` (`descendant`)

) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '节点关系表'

为了防止插入数据出错，我们需要一个存储过程：

CREATE DEFINER = `root`@`localhost` PROCEDURE `AddNode`(`_parent_name` varchar(255),`_node_name` varchar(255))

BEGIN

  DECLARE _ancestor INT(10) UNSIGNED;

  DECLARE _descendant INT(10) UNSIGNED;

  DECLARE _parent INT(10) UNSIGNED;

  IF NOT EXISTS(SELECT node_id From NodeInfo WHERE node_name = _node_name)

  THEN

    INSERT INTO NodeInfo (node_name) VALUES(_node_name);

    SET _descendant = (SELECT node_id FROM NodeInfo WHERE node_name = _node_name);

    INSERT INTO NodeRelation (ancestor,descendant,distance) VALUES(_descendant,_descendant,0);

    

    IF EXISTS (SELECT node_id FROM NodeInfo WHERE node_name = _parent_name)

    THEN

      SET _parent = (SELECT node_id FROM NodeInfo WHERE node_name = _parent_name);

      INSERT INTO NodeRelation (ancestor,descendant,distance) SELECT ancestor,_descendant,distance+1 FROM NodeRelation WHERE descendant = _parent;

    END IF;

  END IF;

END;

然后我们插入一些数据，这里以在论坛里发帖回帖为例：

CALL OrgAndUser.AddNode(NULL,'这是主贴');

CALL OrgAndUser.AddNode('这是主贴','回复主贴1');

CALL OrgAndUser.AddNode('回复主贴1','回复：回复主贴1');

CALL OrgAndUser.AddNode('这是主贴','回复：这是主贴，啥意思');

CALL OrgAndUser.AddNode('这是主贴','回复：挺有意思');

CALL OrgAndUser.AddNode('回复：挺有意思','Reply:回复：挺有意思');

CALL OrgAndUser.AddNode('回复：这是主贴，啥意思','第3层？');

CALL OrgAndUser.AddNode('第3层？','不对，我才是第3层');

SELECT * FROM NodeInfo;

+---------+-----------------------------------+

| node_id | node_name                         |

+---------+-----------------------------------+

|       1 | 这是主贴                           |

|       2 | 回复主贴1                          |

|       3 | 回复：回复主贴1                     |

|       4 | 回复：这是主贴，啥意思                |

|       5 | 回复：挺有意思                      |

|       6 | Reply:回复：挺有意思                |

|       7 | 第3层？|

|       8 | 不对，我才是第3层                    |

+---------+-----------------------------------+

在这里插入图片描述
前面的存储过程会在关系表里插入每条贴子与自身和它上级贴子的关系以及距离：

SELECT * FROM NodeRelation;

+----+----------+------------+----------+

| id | ancestor | descendant | distance |

+----+----------+------------+----------+

|  1 |        1 |          1 |        0 |

|  2 |        2 |          2 |        0 |

|  3 |        1 |          2 |        1 |

|  4 |        3 |          3 |        0 |

|  5 |        2 |          3 |        1 |

|  6 |        1 |          3 |        2 |

|  8 |        4 |          4 |        0 |

|  9 |        1 |          4 |        1 |

| 10 |        5 |          5 |        0 |

| 11 |        1 |          5 |        1 |

| 12 |        6 |          6 |        0 |

| 13 |        5 |          6 |        1 |

| 14 |        1 |          6 |        2 |

| 16 |        7 |          7 |        0 |

| 17 |        4 |          7 |        1 |

| 18 |        1 |          7 |        2 |

| 20 |        8 |          8 |        0 |

| 21 |        7 |          8 |        1 |

| 22 |        4 |          8 |        2 |

| 23 |        1 |          8 |        3 |

+----+----------+------------+----------+

获取闭包表全树或子树

SELECT n3.node_name FROM NodeInfo n1

INNER JOIN NodeRelation n2 ON n1.node_id = n2.ancestor

INNER JOIN NodeInfo n3 ON n2.descendant = n3.node_id

WHERE n1.node_id = 1 AND n2.distance != 0;

+-----------------------------------+

| node_name                         |

+-----------------------------------+

| 回复主贴1                          |

| 回复：回复主贴1                     |

| 回复：这是主贴，啥意思                |

| 回复：挺有意思                      |

| Reply:回复：挺有意思                |

| 第3层？                            |

| 不对，我才是第3层                    |

+-----------------------------------+



SELECT n3.node_name FROM NodeInfo n1

INNER JOIN NodeRelation n2 ON n1.node_id = n2.ancestor

INNER JOIN NodeInfo n3 ON n2.descendant = n3.node_id

WHERE n1.node_name = '回复：这是主贴，啥意思' AND n2.distance != 0;

+---------------------------+

| node_name                 |

+---------------------------+

| 第3层？                    |

| 不对，我才是第3层            |

+---------------------------+

通过关联表的父子关系，去掉自指的记录，使用内连接获取所有子节点。

获取闭包表叶节点

SELECT n1.node_id, n1.node_name FROM NodeInfo n1

INNER JOIN NodeRelation n2 ON n1.node_id = n2.ancestor

GROUP BY n1.node_id, n1.node_name

HAVING COUNT(n2.ancestor) = 1;

+---------+-----------------------------+

| node_id | node_name                   |

+---------+-----------------------------+

|       3 | 回复：回复主贴1                |

|       6 | Reply:回复：挺有意思           |

|       8 | 不对，我才是第3层              |

+---------+-----------------------------+

叶节点的特征是没有子节点，所以它的 ID 只会在关联表的 ancestor 字段出现一次，就是自指的那一次。

获取闭包表父节点

SELECT n1.* FROM NodeInfo AS n1

   INNER JOIN NodeRelation n2 on n1.node_id = n2.ancestor

   WHERE n2.descendant = 8;

+---------+-----------------------------------+

| node_id | node_name                         |

+---------+-----------------------------------+

|       8 | 不对，我才是第3层                    |

|       7 | 第3层？                            |

|       4 | 回复：这是主贴，啥意思                |

|       1 | 这是主贴                           |

+---------+-----------------------------------+

从关系表来倒查，因为关系表里每个节点与其所有上级的关系都记录了。

添加节点

参考前面的存储过程 AddNode(_parent_name, _node_name)
。

删除节点

删除叶节点比较简单，除了在 NodeInfo 表里删除一条记录以外，在关系表 NodeRelation 里把 descendant 值为该叶节点 node_id 的记录都删掉。

DELETE FROM NodeInfo WHERE node_id = 8;

DELETE FROM NodeRelation WHERE descendant = 8;

但是如果要删除中间节点，与前面讨论过的模型一样，需要确定如何处理其子节点或子树。

一种方式是直接找到删除的中间节点的原父节点，让它成为其子节点的新父节点，即所谓爷爷收养孙子；

一种是提升某一个子节点（所谓长子）为新的父节点，将其它子节点的父节点重新指向这个新父节点，所谓父业子承；

还有一种就是把相关的中间节点的子树全部删掉，这就是灭门了……

删除子树

DELETE FROM NodeInfo WHERE node_id = 4;

DELETE FROM NodeRelation AS n1 WHERE n1.descendant IN (SELECT a.descendant FROM (SELECT n2.descendant FROM NodeRelation AS n2 WHERE n2.ancestor = 4) AS a);



SELECT * FROM NodeRelation;

+----+----------+------------+----------+

| id | ancestor | descendant | distance |

+----+----------+------------+----------+

|  1 |        1 |          1 |        0 |

|  2 |        2 |          2 |        0 |

|  3 |        1 |          2 |        1 |

|  4 |        3 |          3 |        0 |

|  5 |        2 |          3 |        1 |

|  6 |        1 |          3 |        2 |

| 10 |        5 |          5 |        0 |

| 11 |        1 |          5 |        1 |

| 12 |        6 |          6 |        0 |

| 13 |        5 |          6 |        1 |

| 14 |        1 |          6 |        2 |

+----+----------+------------+----------+

注意第二条删除语句里，不能直接使用

DELETE FROM NodeRelation AS n1 WHERE n1.descendant IN (SELECT
n2.descendant FROM NodeRelation AS n2 WHERE n2.ancestor = 4);

，MySQL 会报错：

ERROR 1093 (HY000): You can’t specify target table ‘n1’ for update in
FROM clause

。这是因为在 MySQL 中，不能通过嵌套子查询来直接删除或者修改记录，需要通过别名来指定嵌套子查询作为一个临时表。

将已有邻接表转换为闭包表

我们实际的 OA 数据库里有一个汇报关系表，是一个邻接表模型，它大致的字段如下（为了方便我用的临时表 APPROVAL_GROUP_TEMP
）：

CREATE TABLE `APPROVAL_GROUP_TEMP` (

 `ID` decimal(8,0) NOT NULL,

 `FATHERID` decimal(8,0) DEFAULT NULL, -- 上级ID

 `APPROVALGROUPNAME` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, -- 名称

 `SHOWFLAG` decimal(1,0) DEFAULT '1', -- 状态（1:：启用,0：禁用，2：删除）

 PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

我们构建一个闭包表：

CREATE TABLE `AG_Closure` (

  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID',

  `node` varchar(100) NOT NULL,  -- 名称

  `ancestor` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '祖先节点',

  `descendant` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '后代节点',

  `distance` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '相隔层级，>=1',

  PRIMARY KEY (`id`),

  UNIQUE KEY `uniq_anc_desc` (`ancestor`,`descendant`),

  KEY `idx_desc` (`descendant`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

然后使用下面的 CTE SQL 将邻接表的关系转换到闭包表里面（只转换启用的 SHOWFLAG=1
）：

INSERT INTO AG_Closure(node,ancestor,descendant,distance)

WITH RECURSIVE T1(node,ancestor,descendant,distance) AS

(

   SELECT

APPROVALGROUPNAME AS node,

      ID AS ancestor,

      ID AS descendant,

       0  AS distance

   FROM APPROVAL_GROUP_TEMP WHERE SHOWFLAG=1



   UNION ALL



   SELECT

C.APPROVALGROUPNAME AS node,

      T1.ancestor  AS ancestor,

      C.ID          AS descendant,

      T1.distance + 1 AS distance

   FROM APPROVAL_GROUP_TEMP C, T1

       WHERE C.FATHERID = T1.descendant AND C.SHOWFLAG = 1

)

SELECT * FROM T1 ORDER BY T1.descendant

运行后：

SELECT * FROM AG_Closure;

+------+---------------------------------+----------+------------+----------+

| id   | node                            | ancestor | descendant | distance |

+------+---------------------------------+----------+------------+----------+

|    1 | 公司总部                        |    16060 |      16060 |        0 |

|    2 | 研发中心                        |    16062 |      16062 |        0 |

|    3 | 研发中心                        |    16060 |      16062 |        1 |

|    4 | 发行中心                        |    16064 |      16064 |        0 |

|    5 | 发行中心                        |    16060 |      16064 |        1 |

|    6 | 管理中心                        |    16066 |      16066 |        0 |

|    7 | 管理中心                        |    16060 |      16066 |        1 |

|    8 | 人力资源部                      |    16700 |      16700 |        0 |

|    9 | 人力资源部                      |    16883 |      16700 |        1 |

|   10 | 人力资源部                      |    16066 |      16700 |        2 |

|   11 | 人力资源部                      |    16060 |      16700 |        3 |

|   12 | 法务部                          |    16701 |      16701 |        0 |

|   13 | 法务部                          |    16883 |      16701 |        1 |

|   14 | 法务部                          |    16066 |      16701 |        2 |

|   15 | 法务部                          |    16060 |      16701 |        3 |

|   16 | 财务部                          |    16702 |      16702 |        0 |

|   17 | 财务部                          |    16883 |      16702 |        1 |

|   18 | 财务部                          |    16066 |      16702 |        2 |

|   19 | 财务部                          |    16060 |      16702 |        3 |

|   20 | 总裁办                          |    16705 |      16705 |        0 |

|   21 | 总裁办                          |    16066 |      16705 |        1 |

|   22 | 总裁办                          |    16060 |      16705 |        2 |

|   23 | 发行技术部                      |    16711 |      16711 |        0 |

|   24 | 发行技术部                      |    16064 |      16711 |        1 |

|   25 | 发行技术部                      |    16060 |      16711 |        2 |

|   26 | 创新中心                        |    16721 |      16721 |        0 |

|   27 | 创新中心                        |    16060 |      16721 |        1 |

|   28 | 原创IP部                        |    16789 |      16789 |        0 |

|   29 | 原创IP部                        |    16721 |      16789 |        1 |

|   30 | 原创IP部                        |    16060 |      16789 |        2 |

-- ...以下省略

总结

闭包表模型通过使用关系表存储树结构的整个路径，从而能够在不使用递归的情况下快速的查询。但存储整个路径会产生巨大的存储需求，从而成倍地增加存储大小。添加节点的复杂度较高，需要重新计算受影响的节点前后代或距离。

附：
树状结构数据的数据库表设计及使用 - 1. 邻接表
 树状结构数据的数据库表设计及使用 - 2. 路径枚举（Path Enumeration）模型

转：
树状结构数据的数据库表设计及使用 - 3. 闭包表（Closure Table）模型

菠萝y

关注

2
点赞
踩
26

收藏

觉得还不错? 一键收藏
0
评论
树状结构数据的数据库表设计及使用 - 3. 闭包表（Closure Table）模型

本文以 MySQL 为例。文档比较长，故分为5部分发出：闭包表模型闭包表（Closure Table）是一种通过空间换时间的模型，它是用一个专门的关系表（其实这也是我们推荐的归一化方式）来记录树上节点之间的层级关系以及距离。为了防止插入数据出错，我们需要一个存储过程：然后我们插入一些数据，这里以在论坛里发帖回帖为例：前面的存储过程会在关系表里插入每条贴子与自身和它上级贴子的关系以及距离：获取闭包表全树或子树通过关联表的父子关系，去掉自指的记录，使用内连接获取所有子节点。叶节点的特征是没有
复制链接

扫一扫

专栏目录