MySQL存储过程实战-根据子节点，获取所有父节点。

1.业务背景

#目前组织架构中每一个子节点中只有一个parent_id字段，含义为上一级父节点。随着项目越来越复杂，逐渐不满足需求，要求新增一个字段parent_node，包含该子节点的所有父级节点id，使用 ，逗号分割。

2.数据背景

项目中包含多个园区，每个园区包含多个组织架构，组织架构表中由 dept_id 和 campus_id 区分节点，最高级父节点id为 0 。

3.创建存储过程

(1)创建园区遍历存储过程 find_campus

DELIMITER $$
CREATE PROCEDURE find_campus()
BEGIN
-- 		园区总数
			DECLARE n INT;
-- 		下标 默认0
			DECLARE i INT DEFAULT 0;
-- 		待会存储园区id
			DECLARE id VARCHAR(36);
			SELECT COUNT(campus_id) INTO n FROM sys_campus;
			WHILE i<n DO
				SELECT campus_id INTO id FROM sys_campus ORDER BY campus_id LIMIT i , 1 ;
				CALL find_dept(id);
				SET i = i + 1;
			END WHILE;
END$$
DELIMITER;

使用  INTO 可以将查询的值赋值给对应变量。

使用 WHILE 循环遍历所有园区， 记得 SET i = i + 1; 否则会死循环。

存储过程中的  find_dept 在 3.2中创建。

--WHILE 用法，只能在存储过程或函数中使用。

WHILE search_condition DO
	statement_list
END WHILE;

--执行存储过程
CALL find_campus;
--删除存储过程
DROP PROCEDURE IF EXISTS find_campus;

(2) 创建组织架构遍历存储过程 find_dept

DELIMITER $$
CREATE PROCEDURE find_dept(IN id VARCHAR(36))
BEGIN
--    组织架构总数
			DECLARE n INT;
-- 		下标
			DECLARE i INT DEFAULT 0;
-- 		待会存储组织架构id
			DECLARE did VARCHAR(36);
-- 		不需要查询最顶级的节点
			SELECT COUNT(dept_id) INTO n FROM user_dept WHERE dept_id != '0' AND campus_id = id;
			WHILE i<n DO
				SELECT dept_id INTO did FROM user_dept WHERE dept_id != '0' AND campus_id = id ORDER BY dept_id LIMIT i , 1 ;
				CALL find_parent(did ,id);
				SET i = i + 1;
			END WHILE;
END$$
DELIMITER;

调用需要一个入参

存储过程中的 find_parent 在 3.3 创建

--执行存储过程
CALL find_dept(?);
--删除存储过程
DROP PROCEDURE IF EXISTS find_dept;

(3)插入组织架构parent_node字段数据的存储过程 find_parent

DELIMITER $$
CREATE PROCEDURE find_parent(IN id VARCHAR(36) , IN campus_id VARCHAR(36))
BEGIN
    DECLARE parent VARCHAR(36);
		DECLARE parent_node VARCHAR(255);
    
    -- 初始化 parent 变量 对应的 parent_id
    SELECT parent_id INTO parent FROM user_dept WHERE dept_id = id AND campus_id = campus_id;

    -- 循环：当 parent 不是空字符串并且 parent 不为 NULL
    WHILE parent != 0 DO
        --  , 拼接 parent_id
				SELECT CONCAT_WS(',' , parent , parent_node) INTO parent_node;
        
        -- 更新 parent 为新的 parent_id
        SELECT parent_id INTO parent FROM user_dept WHERE dept_id = parent AND campus_id = campus_id;
    END WHILE;
    
		SELECT CONCAT_WS(',' , '0' , parent_node) INTO parent_node;
-- 	设置 parent_node值
		UPDATE user_dept SET parent_node = parent_node WHERE dept_id = id AND campus_id = campus_id;
END$$
DELIMITER ;

使用 CONCAT_WS 拼接，

mysql自带语法CONCAT_WS(separator,string1,string2,...)，但是可以多个字符串用指定的字符串进行拼接（带缝拼接哦）

说明：string1，string2代表的是字符串，而separator代表的是连接其他参数的分隔符，可以是符号，也可以是字符串。如果分隔符为NULL，则结果为NULL。此方法参数可以为NULL。

调用时需要俩个入参 

--执行存储过程
CALL find_parent(? , ?);
--删除存储过程
DROP PROCEDURE IF EXISTS find_parent;

4.调用

首先确保表中已经有 parent_node 字段，没有就新增

alter table user_dept add parent_node VARCHAR(36);

只调用 CALL find_campus; 即可

5.参考文献

 【MySQL存储过程】存储过程的查看与删除_查看存储过程-CSDN博客

mysql存储过程参数输入输出IN/OUT/INOUT_mysql存储过程in可以作为输出参数吗-CSDN博客

mysql 循环三种循环用法 - 简书 (jianshu.com)

mysql 字符串拼接的几种方式-CSDN博客