mysql 创建闭包表_Mysql 闭包表的增删改查实现

-- --------------------- 闭包表的增删改查 ----------------------------------------

-- 节点表

DROP TABLE IF EXISTS `tree_node`;

CREATE TABLE `tree_node` (

`id` int(11) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主键',

`name` varchar(255) DEFAULT NULL COMMENT '节点名称'

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='树节点表';

-- 闭包表

DROP TABLE IF EXISTS `tree_path`;

CREATE TABLE `tree_path` (

`id` int(11) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,

`ancestor` int(11) NOT NULL COMMENT '祖先id',

`descendant` int(11) NOT NULL COMMENT '后代id',

`depth` int(11) NOT NULL COMMENT '后代相对祖先的深度'

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='闭包表';

-- 添加 ----

/* 添加如下结构的树

1

/ \

2 4

/ \ / \

3 5 6 7

/ \

8 9

*/

-- 一、添加节点 ----------------

/*

思路：现在我们要添加一个新的节点3为节点2的子节点，首先是创建3节点，

之后在关系表中添加三条记录：

1.其祖先节点和后代节点为3并且深度为0的关系记录(自己和自己的关系)

2.因为3节点是节点2和1的后代节点，所以我们在关系表中添加两条记录：后代1和祖先3深度为2的关系，后代1和祖先2的深度为1的关系(自己和祖先的关系)

*/

/*INSERT INTO tree_node(`name`) VALUES('节点1'),('节点2'),('节点3'),('节点4'),('节点5'),('节点6'),('节点7'),('节点8'),('节点9');

-- 添加节点关系 (parent_id,new_id):(1,1)、(1,2)、(2,3)、(1,4)、(2,5)、(4,6)、(4,7)、(6,8)、(6,9)

SET @parent_id = 2;-- 父节点id 1,1,2,1,2,4,4,6,6

SET @new_id = 5;-- 新加节点id 1,2,3,4,5,6,7,8,9

INSERT INTO tree_path(ancestor, descendant, depth)

SELECT t.ancestor, @new_id, t.depth + 1

FROM tree_path AS t WHERE descendant = @parent_id-- 父节点所有祖先(包括自己)

UNION

SELECT @new_id, @new_id, 0;-- 自己是自己的父节点，深度为0*/

-- 使用存储过程批量添加

DELIMITER $$

DROP PROCEDURE IF EXISTS `pro_add`;

CREATE PROCEDURE pro_add()

BEGIN

INSERT INTO tree_node(`name`) VALUES('节点1'),('节点2'),('节点3'),('节点4'),('节点5'),('节点6'),('节点7'),('节点8'),('节点9');

SET @parent_ids="1,1,2,1,2,4,4,6,6", @new_ids="1,2,3,4,5,6,7,8,9", @i=1;

SET @count=CHAR_LENGTH(@parent_ids)-CHAR_LENGTH(REPLACE(@parent_ids,',','')) + 1;

WHILE @i <= @count

DO

set @parent_id = SUBSTRING_INDEX(SUBSTRING_INDEX(@parent_ids,',',@i),',',-1);

set @new_id = SUBSTRING_INDEX(SUBSTRING_INDEX(@new_ids,',',@i),',',-1);

-- 依次插入每个成员

INSERT INTO tree_path(ancestor, descendant, depth)

SELECT t.ancestor, @new_id, t.depth + 1

FROM tree_path AS t WHERE descendant = @parent_id-- 父节点所有祖先(包括自己)

UNION

SELECT @new_id, @new_id, 0;-- 自己是自己的父节点，深度为0

SET @i=@i+1;

END WHILE;

END$$

DELIMITER ;

CALL pro_add();

-- 二、查询节点 ---------------- 根据关系表查询即可

-- 查询后代节点(包括自己)

SELECT descendant

FROM tree_path

WHERE ancestor = 6

ORDER BY depth ASC;

-- 查询所有祖先节点(包括自己)

SELECT ancestor

FROM tree_path

WHERE descendant = 7

ORDER BY depth DESC;

-- 三、删除节点 ----------------

/**

假如我们需要删除2节点，也就是连同2节点的后代全部删除，

先找到2节点的所有后代集，在节点表中把他们删掉，

之后需要删除节点2所有后代节点(包括自己)与祖先的关系

*/

-- 删除指定节点

SET @del_id = 2;-- 要删除的id

-- 先其后代节点(包括自己)

DELETE FROM tree_node WHERE id IN (SELECT descendant FROM tree_path WHERE ancestor = @del_id);

-- 再删除其所有后代(包括自己)节点与祖先的关系

DELETE FROM tree_path WHERE descendant IN(SELECT descendant FROM (SELECT descendant, ancestor FROM tree_path) t WHERE ancestor = @del_id);

-- 四、移动子树 ----------------

/**

将节点6 移到 节点1节点下面。

第一步需要删除 6节点的祖先节点集(不包括自己：4和1)和6节点的后代节点集(包括自己：6、8和9)的关系。

第二步重新建立关系，把(1节点的祖先节点集(包括1))X(6节点的后代节点集(包括6))的笛卡尔集插入到关系表中，

其深度为第一个集合的深度加上第二个集合的深度再加一。

*/

-- 1.分离子树,删除节点6的祖先和后代节点的关系

SET @from_id = 6;

SET @to_id = 1;

DELETE FROM tree_path

WHERE

descendant IN (SELECT d.descendant FROM (SELECT * FROM tree_path) d WHERE d.ancestor=@from_id)-- 后代节点(包括自己)

AND

ancestor IN (SELECT a.ancestor FROM (SELECT * FROM tree_path) a WHERE a.descendant=@from_id AND a.ancestor<>a.descendant);-- 祖先节点

-- 2.将上一步分离出的子树用笛卡尔积嫁接到节点1下

INSERT INTO tree_path(ancestor,descendant,depth)

SELECT

super.ancestor,

sub.descendant,

super.depth + sub.depth + 1

FROM

tree_path super

CROSS JOIN

tree_path sub

WHERE

super.descendant=@to_id AND sub.ancestor=@from_id;

-- 五、其它查询

-- 查询根

SELECT ancestor

FROM tree_path

WHERE descendant = 7

ORDER BY depth DESC

LIMIT 1;

-- 查询子节点(一级后代)

SELECT descendant

FROM tree_path

WHERE ancestor = 4 AND depth = 1;

-- 查询父节点(一级祖先)

SELECT ancestor

FROM tree_path

WHERE descendant = 4 AND depth = 1;

-- 查询所有叶子节点与其祖父的关系链

SELECT descendant, GROUP_CONCAT(ancestor ORDER BY depth DESC SEPARATOR ',')

FROM tree_path

-- where descendant = 5

group by descendant;