mysql自定义函数递归查询部门层级

最近有个项目需要在很多前端页面显示当前员工的部门，但是这个部门是显示当前部门的所有父级部门，比如当前为研发部，在前台页面需要显示为

“集团公司>子公司>研发部” 的格式，因为很多页面都需要这种数据，在查询时关联查询可能比较麻烦，所以选择自定义一个mysql函数来查询这种格式。

现在需要的是从子级查询父级部门，是从下往上查，可能父级部门只有一个，也可能有多个父级部门，所以我们需要for循环来递归查询。

为了举个栗子，新建表结构及插入数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sa_org
-- ----------------------------
DROP TABLE IF EXISTS `sa_org`;
CREATE TABLE `sa_org`  (
  `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `p_org_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `org_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sa_org
-- ----------------------------
INSERT INTO `sa_org` VALUES ('27193728124848239', '0', '集团公司');
INSERT INTO `sa_org` VALUES ('27193728124848240', '27193728124848239', '子公司1');
INSERT INTO `sa_org` VALUES ('27193728124848241', '27193728124848240', '合规部');

SET FOREIGN_KEY_CHECKS = 1;

这里插入三条数据，比如当前某个员工是合规部，需要显示为如下格式：

这里我们设置最顶级的部门id为0，当当前部门的id为0时默认退出循环，所以我们可以这样写函数：

DROP FUNCTION IF EXISTS getOrgParent;

CREATE  FUNCTION `getOrgParent`(orgId varchar(100)) RETURNS varchar(1000) CHARSET utf8
        
BEGIN 
DECLARE orgNamePath VARCHAR(1000);
DECLARE pOrgId VARCHAR(32);
DECLARE orgName VARCHAR(128);

SET orgNamePath = '';
SET pOrgId = orgId;
set orgName = '';

WHILE pOrgId <> '0'
DO

SELECT p_org_id, org_name INTO pOrgId, orgName FROM sa_org WHERE id = pOrgId;

IF orgNamePath = '' THEN
    SET orgNamePath = orgName;
ELSE
    SET orgNamePath = CONCAT(orgName, '>', orgNamePath);
END IF;

END WHILE;
RETURN orgNamePath;
END

执行完成，查询试一下：

select getOrgParent('27193728124848241')

select getOrgParent('27193728124848240')

 可以看到实现了我们的需求，在查询表格数据时，直接使用这个函数就可以将org_id变为部门层级展示了。

但是当我们输入一个不存在于这个表的数据时，会发现这条查询一直在执行，构成了死循环，所以我们改成如下：

drop  FUNCTION if EXISTS `getOrgParent`;
CREATE FUNCTION `getOrgParent`(orgId varchar(100)) RETURNS varchar(1000) CHARSET utf8
BEGIN 
DECLARE orgNamePath VARCHAR(1000);
DECLARE pOrgId VARCHAR(32);
DECLARE orgName VARCHAR(128);
DECLARE count INT;

SET orgNamePath = '';
SET pOrgId = orgId;
set orgName = '';
set count = 1;

WHILE pOrgId <> '0' and count <> 0
DO

SELECT p_org_id, org_name, count(1) INTO pOrgId, orgName, count FROM sa_org WHERE id = pOrgId;

IF orgNamePath = '' THEN
    SET orgNamePath = orgName;
ELSE
    SET orgNamePath = CONCAT(orgName, '>', orgNamePath);
END IF;

END WHILE;
RETURN orgNamePath;
END

 可以看到即使现在这条数据不存在也不会死循环了。