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;

推荐看图理解连接：闭包表图解