日常开发中我们经常会遇到树形结构数据处理,一般表结构通常会常用id,pid这种设计方案。
之前用oracle、sqlServer数据库,用相应的语法即可获取树形结构数据(oracel:connect by prior ;sqlServer:with...as ...)。
最近一个项目数据库用的是mysql,需求中含有获取树形结构数据的接口,由于之前没怎么用过mysql,于是第一时间就是查看mysql语法,看看有没有类似于oracle或sqlserver的递归语法,结果是没有,后来决定自定义数据库函数(暂时解决了需求)。
1.自定义递归函数
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Function structure for `getPartyChildOrg`
-- ----------------------------
DROP FUNCTION IF EXISTS `getPartyChildOrg`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `getPartyChildOrg`(orgid BIGINT) RETURNS varchar(4000) CHARSET utf8
BEGIN
DECLARE oTemp VARCHAR(4000);
DECLARE oTempChild VARCHAR(4000);
SET oTemp = '';
SET oTempChild = CAST(orgid AS CHAR);
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(id) INTO oTempChild FROM sub_party_orginfo WHERE logic_delete = 0 and FIND_IN_SET(pid,oTempChild) > 0;
END WHILE;
RETURN oTemp;
END
;;
DELIMITER ;
暂时满足了需求,后期数据量大了,再加上主键生成策略生成的主键长度比较长,函数返回结果长度受VARCHAR最大长度限制,该方案会失效,导致查询的数据不全。
2.自定义存储过程,将查询的结果存放到临时表中。
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Procedure structure for `findPartyOrgChildList`
-- ----------------------------
DROP PROCEDURE IF EXISTS `findPartyOrgChildList`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `findPartyOrgChildList`(IN pid VARCHAR(20))
BEGIN
DECLARE v_org BIGINT(20);
DECLARE done INTEGER DEFAULT 0;
-- 查询结果放入游标中
DECLARE C_org CURSOR FOR SELECT d.id
FROM sub_party_orginfo d
WHERE d.pid = pid;
DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
SET @@max_sp_recursion_depth = 10;
-- 传入的组织id写入临时表
INSERT INTO temp_party_org VALUES (pid);
OPEN C_org;
FETCH C_org INTO v_org;
WHILE (done=0)
DO
-- 递归调用,查找下级
CALL findPartyOrgChildList(v_org);
FETCH C_org INTO v_org;
END WHILE;
CLOSE C_org;
END
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for `findPartyOrgList`
-- ----------------------------
DROP PROCEDURE IF EXISTS `findPartyOrgList`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `findPartyOrgList`(IN pid BIGINT(20))
BEGIN
DROP TEMPORARY TABLE IF EXISTS temp_party_org;
-- 创建临时表
CREATE TEMPORARY TABLE temp_party_org(id BIGINT(20));
-- 清空临时表数据
DELETE FROM temp_party_org;
-- 发起调用
CALL findPartyOrgChildList(pid);
END
;;
DELIMITER ;