使用MySQL存储过程实现递归查询

我的图书，我的馆 2020-08-13

展开全文

“ 本文将结合实例介绍了如何使用MySQL的存储过程功能来实现对数据库数据的递归查询。”

—

场景

在开发企业级应用的时候，通常会涉及到对企业组织结构信息的管理，通过对组织结构层级关系的描述可以对数据访问权限进行有效的控制。例如：总公司的操作员能够查看到所有分公司的数据，而分公司操作员则不能查看其他分公司的数据。

在数据库设计当中，通常我们会添加一个 parent_id 字段来指向当前单位的上级单位，以此描述企业组织结构的层级关系。例如：“热力分公司” 的上级单位是 “热力总公司”，则它的 parent_id 为 “热力总公司” 的 ID。

unit 表数据

在进行数据库查询的时候，就可以使用如下语句查询到某个单位的所有下一级单位。

SELECT * FROM unit WHERE parent_id = 1

但是，事情往往没有那么简单，如果想要改变某个单位的上级单位，这时候会有一个约束条件：不能够将某个单位的上级单位修改为该单位自身以及其下属单位。因为这会导致循环引用的问题的出现。

组织结构管理界面

—

实现

要想满足上述的约束条件，我们就不能允许修改的 parent_id 为当前单位的ID或者其所有下属单位的ID。由于企业的组织结构的层级数量往往是不确定的，所以要想查询到某个单位所有下属单位的ID，必然是需要进行一个递归查询的，通过不断的查询指定单位的下属单位ID，下属单位的下属单位ID，下属单位的下属单位的下属单位ID等等，直到再没有下属单位为止。

要想实现这样的递归查询，我们可以使用MySQL提供的存储过程功能。存储过程功能和编程语言当中函数其实是比较类似的，里面包含了一条或多条的数据库操作语句，一个存储过程可以去调用另外一个存储过程，也可以自己调用自己。因此利用存储过程的能力，可以很轻易的实现递归查询的功能。

正文开始

首先，我们定义一个可以递归调用的存储过程，用来查询指定单位的下一级单位，该存储过程的入参是 parentId，也就是指定单位的ID。

存储过程会查找 unit 表中字段 parent_id 值为 parentId 的数据，并将找到的下属单位ID存放到一张临时表（tmp_unit）当中，通过一个循环遍历当前单位的下属单位ID列表，继续调用存储过程 findChildUnit 自身，逐层的查找所有下属单位。

CREATE PROCEDURE `findChildUnit`(IN parentId INT)BEGIN  DECLARE v_unit_id INT DEFAULT -1;    # 循环终止标志  DECLARE done INT DEFAULT 0;    # 查询当前部门的子部门ID列表  DECLARE cur_unit CURSOR FOR SELECT id  FROM unit WHERE parent_id = parentId;  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;    INSERT INTO tmp_unit VALUES(parentId);    # 打开游标（游标指向有待遍历的部门ID）  OPEN cur_unit;  FETCH cur_unit INTO v_unit_id;  WHILE (done = 0)  DO    CALL findChildUnit(v_unit_id);    FETCH cur_unit INTO v_unit_id;  END WHILE;END

定义一个存储过程需要使用 CREATE PROCEDURE 关键字，后面紧跟着的是存储过程的名称，调用的使用可以使用 CALL 关键字进行调用。再后面的(IN parentId INT) 部分是存储过程的参数列表，类似与函数的参数列表，IN关键字描述了该 parentId 是一个入参，另外还有关键字 OUT 表示参数为输出的变量，INOUT则表示既可以该参数即作为输出变量也同时作为输入变量。

紧接着，我们还要创建另外一个存储过程，作为上述递归调用的存储过程的入口，在这个存储过程当中创建一张临时表来存储找到的下属单位的ID。

CREATE PROCEDURE `findChildUnitRecursive`(IN parentId INT)BEGIN # 创建临时表，存储查询结果 DROP TEMPORARY TABLE IF EXISTS tmp_unit; CREATE TEMPORARY TABLE tmp_unit(unit_id INT); DELETE FROM tmp_unit; # 调用子存储过程 CALL `findChildUnit`(parentId);

# 输出结果 SELECT DISTINCT unit_id FROM tmp_unit ORDER BY unit_id;END

最后，如果项目采用的是 MyBatis+MyBatis Plus作为数据持久层框架的话可以采用以下方法调用存储过程来获得所有下属单位的ID列表。

@Repositorypublic interface UnitMapper extends BaseMapper<Unit> {
    @Select('CALL findChildUnitRecursive(#{ parentId })')    List<Integer> getSubUnitRecursive(Integer parentId);
}

正文结束

—

实践

纸上得来终觉浅，绝知此事要躬行。看完文章不妨自己也实践起来试试看。以下是用于测试的数据库表结构的数据。

CREATE TABLE `unit` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '单位ID', `name` varchar(128) NOT NULL COMMENT '单位名称', `enable` bit(1) NULL COMMENT '是否启用', `parent_id` int NULL COMMENT '上级单位', PRIMARY KEY (`id`));

INSERT INTO `unit` VALUES (1, '热力总公司', b'1', NULL);INSERT INTO `unit` VALUES (2, '热力分公司', b'1', 1);INSERT INTO `unit` VALUES (3, '热力所1', b'1', 2);INSERT INTO `unit` VALUES (4, '热力站1', b'1', 3);INSERT INTO `unit` VALUES (5, '热力站2', b'1', 3);INSERT INTO `unit` VALUES (9, '热力站3', b'1', 3);