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

最新推荐文章于 2023-03-25 14:41:00 发布

菠萝y

最新推荐文章于 2023-03-25 14:41:00 发布

阅读量2.4k

点赞数 2

分类专栏：设计模式 mysql 数据结构与算法文章标签：数据库自然语言处理 database

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

版权

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

31 篇文章 1 订阅

订阅专栏

mysql

23 篇文章 3 订阅

订阅专栏

设计模式

15 篇文章 0 订阅

订阅专栏

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

📝 邻接表（Adjacency List）模型 

🎨 路径枚举（Path Enumeration）模型

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

🤹 嵌套集（Nested Set）模型

🧮 性能比较与分析

路径枚举模型

路径枚举（Path Enumeration）模型通过一个字符串字段记录保存从根节点到本节点经过的所有节点枚举。

创建一个表，为了方便就把人员信息和路径信息放在一起了。

CREATE TABLE Personnel_OrgChart(

  emp_name CHAR(10) NOT NULL,

  emp_id CHAR(1) NOT NULL PRIMARY KEY,

  path_string VARCHAR(500) NOT NULL

);





INSERT INTO Personnel_OrgChart

VALUES('Albert','A','A'),('Bert','B','AB'),

   ('Chuck','C','AC'),('Donna','D','ACD'),

   ('Eddie','E','ACE'),('Fred','F','ACF');



SELECT * FROM Personnel_OrgChart ORDER BY emp_id;

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

| emp_name | emp_id | path_string |

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

| Albert   | A      | A           |

| Bert     | B      | AB          |

| Chuck    | C      | AC          |

| Donna    | D      | ACD         |

| Eddie    | E      | ACE         |

| Fred     | F      | ACF         |

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

在这里插入图片描述路径枚举模型的特点是通过将所有祖先的信息联合成一个字符串，并保存为每个节点的一个属性。字符串的构建根据喜好或需要。上面的 path_string 也可以写成 ‘A/C/E’ 或 ‘A_C_E’ 这样的。emp_id 也可以是数字，例如 ‘1/3/5’ 。

路径枚举模型的问题与邻接表类似，在没有限制的情况下可能出现 ‘ACEA’ 这样的回环路径，删除一个中间节点可能造成孤立子树，而且插入中间节点会导致多个节点的路径需要修改。

获取路径枚举表子树

一般开发者会直接的使用下面的语句来获取某个子树：

SELECT * FROM Personnel_OrgChart WHERE path_string LIKE '%C%';

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

| emp_name | emp_id | path_string |

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

| Chuck    | C      | AC          |

| Donna    | D      | ACD         |

| Eddie    | E      | ACE         |

| Fred     | F      | ACF         |

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

这里的问题是使用通配符 %
查找会扫描整个表，对于数据很多的表来说速度就很慢了。

获取路径枚举表父节点

SELECT P2.*

FROM Personnel_OrgChart AS P1,

Personnel_OrgChart AS P2

WHERE P1.emp_id = 'F'

AND POSITION(P2.path_string IN P1.path_string)= 1;

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

| emp_name | emp_id | path_string |

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

| Albert   | A      | A           |

| Chuck    | C      | AC          |

| Fred     | F      | ACF         |

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

添加节点

在路径枚举表中添加叶节点比较简单，直接插入一条数据即可：

INSERT INTO Personnel_OrgChart VALUES('Gary','G','ABG');

但是如果要插入到某个节点之前，那么被插入的节点和其子节点的路径都需要修改，例如在 Chuck 上面插入 Gary 作为 Chuck 父节点：

INSERT INTO Personnel_OrgChart VALUES('Gary','G','AG');

UPDATE Personnel_OrgChart SET path_string = REPLACE(path_string, 'AC', 'AGC') WHERE path_string LIKE 'AC%';



SELECT * FROM Personnel_OrgChart;

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

| emp_name | emp_id | path_string |

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

| Albert   | A      | A           |

| Bert     | B      | AB          |

| Chuck    | C      | AGC         |

| Donna    | D      | AGCD        |

| Eddie    | E      | AGCE        |

| Fred     | F      | AGCF        |

| Gary     | G      | AG          |

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

删除节点

与添加节点类似，删除叶节点比较简单，直接删除记录即可：

DELETE FROM Personnel_OrgChart WHERE path_string = 'AGCD';

但如果要删除某个中间节点，例如 Chuck，那么我们与邻接表一样需要确定原来 Chuck 的子节点们该如何处理。

一种方式是直接找到删除的中间节点的原父节点，让它成为其子节点的新父节点，即所谓爷爷收养孙子；
一种是提升某一个子节点（所谓长子）为新的父节点，将其它子节点的父节点重新指向这个新父节点，所谓父业子承；

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

以下 SQL 采用第一种方式处理：

DELETE FROM Personnel_OrgChart WHERE emp_id = 'C';

UPDATE Personnel_OrgChart SET path_string = REPLACE(path_string, 'C', '') WHERE path_string LIKE '%C%';



SELECT * FROM Personnel_OrgChart;

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

| emp_name | emp_id | path_string |

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

| Albert   | A      | A           |

| Bert     | B      | AB          |

| Donna    | D      | AGD         |

| Eddie    | E      | AGE         |

| Fred     | F      | AGF         |

| Gary     | G      | AG          |

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

删除子树

知道某个节点，删除其子树与获取子树方式类似，使用 % 通配符即可：

DELETE FROM Personnel_OrgChart WHERE path_string LIKE '%G%';

将已有邻接表转换为路径枚举表

我们实际的 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_PathEnum` (

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

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

 `nodeid` INT(10) COMMENT '节点ID',

 `path_string` VARCHAR(500) NOT NULL COMMENT '相隔层级，>=1',

 PRIMARY KEY (`id`)

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

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

INSERT INTO AG_PathEnum(node,nodeid,path_string)

WITH RECURSIVE T1(node,nodeid,path_string) AS

(

   SELECT

T0.APPROVALGROUPNAME AS node,

      T0.ID AS nodeid,

      CAST(T0.ID AS char(500)) AS path_string

   FROM APPROVAL_GROUP_TEMP AS T0 WHERE T0.SHOWFLAG = 1



   UNION ALL



   SELECT

C.APPROVALGROUPNAME AS node,

      C.ID          AS nodeid,

      CONCAT(T1.path_string,"/",C.ID) AS path_string

   FROM APPROVAL_GROUP_TEMP C, T1

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

)

SELECT * FROM T1 WHERE T1.path_string LIKE '16060%' GROUP BY T1.nodeid,T1.node,T1.

运行后：

SELECT * FROM AG_PathEnum;

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

| id  | node                            | nodeid | path_string                                     |

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

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

|   2 | 研发中心                        |  16062 | 16060/16062                                     |

|   3 | 发行中心                        |  16064 | 16060/16064                                     |

|   4 | 管理中心                        |  16066 | 16060/16066                                     |

|   5 | 人力资源部                      |  16700 | 16060/16066/16883/16700                         |

|   6 | 法务部                          |  16701 | 16060/16066/16883/16701                         |

|   7 | 财务部                          |  16702 | 16060/16066/16883/16702                         |

|   8 | 总裁办                          |  16705 | 16060/16066/16705                               |

-- ...以下省略

总结

路径枚举的设计方式能够很方便地根据节点的层级排序，因为路径中分隔两边的节点间的距离永远是1，因此通过比较路径字符串长度就能知道层级的深浅。但也有如下缺点：

1、不能确保路径的格式总是正确或者路径中的节点确实存在（中间节点被删除的情况，无外键约束）；

2、要依赖高级程序代码来维护路径中的字符串，并且验证字符串的正确性的开销很大；

3、路径 VARCHAR 的长度很难确定。无论 VARCHAR 的长度设为多大，都存在不能够无限扩展的情况。

附：
树状结构数据的数据库表设计及使用 - 1. 邻接表

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

菠萝y

关注

2
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
树状结构数据的数据库表设计及使用 - 2. 路径枚举（Path Enumeration）模型

本文以 MySQL 为例。文档比较长，故分为5部分发出：路径枚举模型路径枚举（Path Enumeration）模型通过一个字符串字段记录保存从根节点到本节点经过的所有节点枚举。创建一个表，为了方便就把人员信息和路径信息放在一起了。路径枚举模型的特点是通过将所有祖先的信息联合成一个字符串，并保存为每个节点的一个属性。字符串的构建根据喜好或需要。上面的 path_string 也可以写成 ‘A/C/E’ 或 ‘A_C_E’ 这样的。emp_id 也可以是数字，例如 ‘1/3/5’ 。路径枚举模型的问题与
复制链接

扫一扫

专栏目录