mysql树形结构查询sql mysql树形结构设计

转载

mob6454cc6d3e23 2023-10-18 14:01:03

文章标签 mysql树形结构查询sql mysql左右值树 MySQL 数据库表右值 文章分类 MySQL 数据库

在关系型数据库中设计树形的数据结构一直是一个十分考验开发者能力的，最经常使用的方案有主从表方案和继承关系(parent_id)方案。主从表方案的最年夜缺点是树形结构的深度扩展困难，一般来说都是固定的，适合深度固定的需求。继承关系方案设计和实现自然而然，很是直观和便利。缺点固然也是很是的突出：由于直接地记录了节点之间的继承关系，因此对Tree的任何 CRUD操作都将是低效的，这主要归根于频繁的“递归”操作，递归过程不竭地拜候数据库，每次数据库IO城市有时间开销。因此这种方案适合Tree规模相对较小的情况，我们可以借助于缓存机制来做优化，将Tree的信息载入内存进行措置，避免直接对数据库IO操作的性能开销。

理想中树形结构应该具备如下特征：检索遍历过程简单高效；节点增删改查CRUD操作高效；数据存储冗余度小、直观性强。笔者在查阅网上相关资料之后整理了一个基于左右值编码的树形结构的数据库表结构设计方案，并在MySQL数据库中实现。

首先我们记住以下这张图

MySQL基于左右值编码的树形数据库表结构设计-1.jpg (49.27 KB, 下载次数: 0)

2018-8-11 18:57 上传

图一左右值属性结构

采取深度优先遍历给树中的每个节点分派两个值，一个左值和一个右值。节点左边的值比该节点的所有子孙节点值都要小，节点右边的值比该节点的所有子孙节点值都要年夜。例如：

B左边的值为2，其比Hell Mayes的所有子孙节点的值都要小(D[3,4]、E[5,10]、I[6,7]、J[8,9]、F[11,12])

B右边的值为13，其比Hell Mayes的所有子孙节点的值都要年夜(D[3,4]、E[5,10]、I[6,7]、J[8,9]、F[11,12])

有了这个规则整棵树的结构通过左值和右值存储了下来。

接下来我们在MySQL中建表，并实现整棵树的CURD体例

建立表结构

CREATE TABLE `tree` (
`node_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`lft` int(11) DEFAULT NULL,
`rgt` int(11) DEFAULT NULL,
PRIMARY KEY (`node_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `tree` VALUES
(1,'A',1,20),
(2,'B',2,13),
(3,'C',14,19),
(4,'D',3,4),
(5,'E',5,10),
(6,'F',11,12),
(7,'G',15,16),
(8,'H',17,18),
(9,'I',6,7),
(10,'J',8,9);

准备工作就绪。

1)获取某个节点的子孙节点

以B为例：

SELECT* FROM Tree WHERE Lft BETWEEN 2 AND 13 ORDER BY Lft ASC

MySQL基于左右值编码的树形数据库表结构设计-2.jpg (8.63 KB, 下载次数: 0)

2018-8-11 18:57 上传

图二 B的子孙节点

某个节点到底有几多的子孙节点呢？通过该节点的左、右值我们可以将其子孙节点圈进来，则子孙总数 = (右值 – 左值– 1) / 2，以B为例，其子孙总数为：(13–2 – 1) / 2 = 5。同时，为了更为直观地展现树形结构，我们需要知道节点在树中所处的条理，通过左、右值的SQL查询即可实现。以B为例：

SELECT COUNT(*) FROM Tree WHERE Lft <= 2 AND Rgt >=13

结果为2，表白B处于该树的第二层。为了便利描述，我们可以为Tree建立一个视图，添加一个条理字段，该字段值可以写一个自界说函数来计算，函数界说如下：

界说计算指定节点所在层的函数CountLayer

CREATE DEFINER=`root`@`localhost` FUNCTION `CountLayer`(p_node_id int) RETURNS int(11)
BEGIN
declare p_result,p_lft,p_rgt int default 0;
if exists (select 1 from tree where node_id=p_node_id) then
begin
select lft, rgt into p_lft, p_rgt from tree where node_id=p_node_id;
select count(*) into p_result from tree where lft <= p_lft and rgt >= p_rgt;
end;
return p_result;
end if;
RETURN 0;

END

函数名称为CountLayer，需传入指定节点的id。此时我们可以基于方才界说的层计算函数来建立一个包含条理字段的试图。

界说条理试图Tree_View

CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `tree_view` AS
SELECT
`tree`.`node_id` AS `node_id`,
`tree`.`name` AS `name`,
`tree`.`lft` AS `lft`,
`tree`.`rgt` AS `rgt`,
COUNTLAYER(`tree`.`node_id`) AS `layer`
FROM
`tree`
ORDER BY `tree`.`lft`

MySQL基于左右值编码的树形数据库表结构设计-3.jpg (15.95 KB, 下载次数: 0)

2018-8-11 18:57 上传

图三各节点所处的条理

此时我们来建立一个存储过程，用来获取给定节点的所有子孙节点和每个节点所在的层。

获取所有子孙节点的存储过程GetChildrenNodeList

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetChildrenNodeList`(in p_node_id int)
BEGIN
declare p_lft,p_rgt int default 0;
if exists (select node_id from tree where node_id=p_node_id) then
begin
select lft,rgt into p_lft,p_rgt from tree where node_id=p_node_id;
select * from Tree_View where lft between p_lft and p_rgt order by layer, lft;
end;
end if;

END

查询B的所有子孙节点

call GetChildrenNodeList(2);

可以获得

MySQL基于左右值编码的树形数据库表结构设计-4.jpg (11.06 KB, 下载次数: 0)

2018-8-11 18:57 上传

图四 B的所有子孙节点及相应的层

可以计算其子孙节点，当让也可以计算其节点。

建立获取所有父节点的存储过程GetParentNodePath

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetParentNodePath`(in p_node_id int)
BEGIN
declare p_lft,p_rgt int default 0;
if exists (select node_id from tree where node_id=p_node_id) then
begin
select lft,rgt into p_lft,p_rgt from tree where node_id=p_node_id;
select * from Tree_View where lftp_rgt order by layer,lft asc;
end;
end if;
END

以E节点为例：

call GetParentNodePath(5);

可以获得

MySQL基于左右值编码的树形数据库表结构设计-5.jpg (7.03 KB, 下载次数: 0)

2018-8-11 18:57 上传

图五 E的所有父节点

2)在某个节点下插入一个子节点。

仔细观察图一，我们以在H下添加一个节点K为例。K节点的左值为H节点的右值，K节点的右值为其左值+1，其他所有右值年夜于等于K的左值的节点的右值须+2，所有左值年夜于等于K的左值的节点的左值须+2。图示如下：

MySQL基于左右值编码的树形数据库表结构设计-6.jpg (50.34 KB, 下载次数: 0)

2018-8-11 18:57 上传

图六新增节点

我们将其界说为存储过程：

添加节点的存储过程AddSubNode

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddSubNode`(in p_node_id int,in p_node_name varchar(50))
BEGIN
declare p_rgt int default 0;
if exists(select node_id from tree where node_id=p_node_id) then
begin
SET AUTOCOMMIT=0;
START TRANSACTION;
select rgt into p_rgt from tree where node_id=p_node_id;
update tree set rgt=rgt+2 where rgt>=p_rgt;
update tree set lft=lft+2 where lft>=p_rgt;
insert into tree(name,lft,rgt) values(p_node_name,p_rgt,p_rgt+1);
COMMIT;
end;
end if;
END

挪用AddSubNode存储过程

call AddSubNode(8,'K');

MySQL基于左右值编码的树形数据库表结构设计-7.jpg (13.95 KB, 下载次数: 0)

2018-8-11 18:57 上传

图七 K节点成功插入

3)删除节点

删除节点是新增节点的逆向过程。

建立删除节点存储过程DelNode

CREATE DEFINER=`root`@`localhost` PROCEDURE `DelNode`(in p_node_id int)
BEGIN
declare p_lft,p_rgt int default 0;
if exists(select p_node_id from tree where node_id =p_node_id) then
START TRANSACTION;
select lft,rgt into p_lft,p_rgt from tree where node_id=p_node_id;
delete from tree where lft>=p_lft and rgt<=p_rgt;
update tree set lft=lft-(p_rgt - p_lft + 1) where lft > p_lft;
update tree set rgt=rgt-(p_rgt - p_lft + 1) where rgt > p_rgt;
COMMIT;
end if;

END

以删除C节点为例

call DelNode(3);

再次查询tree结果为：

MySQL基于左右值编码的树形数据库表结构设计-8.jpg (10.73 KB, 下载次数: 0)

2018-8-11 18:57 上传

图八 C及其所有的子节点都被删除

到此我们已将基于左右值编码的树形数据库表结构设计的基来源根基理介绍完了。固然，对节点的操作还远不止这些，感兴趣的朋友可以自己脱手实现。

诚然，这个方案也有其不足之处：节点的添加、删除及修改价格较年夜，将会涉及到表中多方面数据的改动。可是，在消除递归操作的前提下实现了无限分组，并且查询条件是基于整形数字的比较，效率很高。所以，该方案比较实用与查询较多，变动不年夜的场景。

不足之处，躬请指正。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。