- 递归
现有一张部门表,需要将部门做树形结构展示,树形展示字段为ORG_PATH,假设部门A的ORG_ID为1,部门B的ORG_ID为2,部门C的ORG_ID为3,B为C的父级,A为B的父级,那么C的ORG_PATH为: 1|2|3|
下面直接贴出递归代码(mysql存储过程):
CREATE DEFINER=`root`@`localhost` PROCEDURE `getOrgPath`(parentOrgId BIGINT)
BEGIN
DECLARE ids LONGTEXT DEFAULT '0';
DECLARE parentOrgPath VARCHAR(512) DEFAULT '';
DECLARE orgId BIGINT(20);
DECLARE i INT DEFAULT 1;
DECLARE IND INT;
IF parentOrgId IS NULL THEN
SELECT GROUP_CONCAT(ORG_ID) INTO ids FROM t_organization_inf WHERE PARENT_ORG IS NULL;
SELECT COUNT(*) INTO IND FROM t_organization_inf WHERE PARENT_ORG IS NULL;
ELSE
SELECT GROUP_CONCAT(ORG_ID) INTO ids FROM t_organization_inf WHERE PARENT_ORG = parentOrgId;
SELECT COUNT(*) INTO IND FROM t_organization_inf WHERE PARENT_ORG = parentOrgId;
SELECT ORG_PATH INTO parentOrgPath FROM t_organization_inf WHERE ORG_ID = parentOrgId;
END IF;
IF ids <> '0' THEN
SET @@max_sp_recursion_depth = 10; -- 设置递归最大深度,不设置报错
WHILE i <= IND DO
SET orgId = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(ids,',',i),',',-1));
UPDATE t_organization_inf SET ORG_PATH = CONCAT(parentOrgPath,orgId,'|') WHERE ORG_ID = orgId;
CALL getOrgPath(orgId); -- 调用自身递归
SET i = i + 1;
END WHILE;
END IF;
END
- 出现的问题及解决(group_concat)
在测试执行该存储过程的时候发现有一些部门的ORG_PATH字段仍是默认的NULL,一步一步查才发现 SELECT GROUP_CONCAT(ORG_ID) INTO ids FROM t_organization_inf WHERE PARENT_ORG IS NULL;
中有一级部门的子级部门应该是1400多个,但是变量 ids
中只有800多个,这就是GROUP_CONCAT
的长度不够了,百度一番才知
执行SHOW VARIABLES LIKE 'group_concat_max_len'
查看当前GROUP_CONCAT的最大长度,
执行
set SESSION group_concat_max_len = 10240
或
SET GLOBAL group_concat_max_len = 10240;
将长度调大。
问题解决后再运行存储过程,一路OK!