MySQL 函数 递归 计算嵌套树深度

MySQL使用函数进行递归查询计算嵌套树深度

时间: 2021年03月27日

问题

需求: 给定一个组织部门的id, 计算这个组织部门深度
数据库使用MySQL, 计算嵌套组织的深度有两个办法:

1. 外部代码循环查询(不推荐)
2. 使用MySQL函数和存储过程进行递归查询

使用MySQL函数和存储过程计算嵌套组织深度

CREATE TABLE `organization` (
  `id` BIGINT NOT NULL COMMENT 'id主键',
  `pid` BIGINT NOT NULL COMMENT '父级组织id',
  `name` varchar(64) NOT NULL COMMENT '组织名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='组织表';

-- 初始化数据
INSERT INTO organization (id, pid, `name`) VALUE(1, 0, '总部');
INSERT INTO organization (id, pid, `name`) VALUE(2, 1, '研发部');
INSERT INTO organization (id, pid, `name`) VALUE(3, 1, '产品部');
INSERT INTO organization (id, pid, `name`) VALUE(4, 1, '财务部');
INSERT INTO organization (id, pid, `name`) VALUE(5, 2, '后端');
INSERT INTO organization (id, pid, `name`) VALUE(6, 2, '前端');
INSERT INTO organization (id, pid, `name`) VALUE(7, 5, 'Java');
INSERT INTO organization (id, pid, `name`) VALUE(8, 5, 'Python');
INSERT INTO organization (id, pid, `name`) VALUE(9, 6, 'Vue');
INSERT INTO organization (id, pid, `name`) VALUE(10, 6, 'React');

-- 创建函数 countLayer
DELIMITER //
DROP FUNCTION IF EXISTS countLayer//
CREATE FUNCTION countLayer(id BIGINT) RETURNS INT
BEGIN
    DECLARE counter INT DEFAULT 0;
    SET max_sp_recursion_depth = 255;
    CALL countLayerHelper(id, counter);
    RETURN counter - 1;
END//

-- 创建存储过程 countLayerHelper
DROP PROCEDURE IF EXISTS countLayerHelper;
CREATE PROCEDURE countLayerHelper(IN id BIGINT, INOUT counter INT)
BEGIN
    IF EXISTS (SELECT 1 FROM organization o WHERE o.id = id) THEN
    BEGIN
        DECLARE pid BIGINT DEFAULT 0;
        DECLARE done BOOLEAN DEFAULT FALSE;
        DECLARE _cursor CURSOR FOR SELECT o.pid FROM organization o WHERE o.id = id;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        SET counter = counter + 1;
        OPEN  _cursor;
        read_loop: LOOP
            FETCH _cursor INTO pid;
            IF done THEN LEAVE read_loop;
            END IF;
            CALL countLayerHelper(pid, counter);
        END LOOP;
        CLOSE _cursor;
    END;
    END IF;
END;

组织部门架构

图1 组织部门架构

测试

SELECT countLayer(0)
结果: -1

SELECT countLayer(-1)
结果: -1

SELECT countLayer(11)
结果: -1

SELECT countLayer(1)
结果: 0

SELECT countLayer(3)
结果: 1

SELECT countLayer(6)
结果: 2

SELECT countLayer(8)
结果: 3

注意

如果形参或自定义变量和查询的字段同名则需要查询时将表重命名
SELECT 1 FROM organization o WHERE o.id = id;
SELECT o.pid FROM organization o WHERE o.id = id;