Mysql (自定义函数)递归查询父节点和子节点

首先检查创建函数的功能是否开启,检查是否开启创建功能的SQL如下:

-- 查看是否开启创建函数的功能
show variables like '%func%';
-- 开启创建函数的功能
set global log_bin_trust_function_creators = 1;

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓建函数↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

一、查父集合

Sql代码:

DELIMITER $
CREATE DEFINER=`root`@`%` FUNCTION `getParentList`(`rootId` LONGTEXT) RETURNS longtext CHARSET utf8mb4
BEGIN 
DECLARE fid LongText default ''; 
DECLARE str LongText default rootId; 
 
WHILE rootId is not null  do 
	SET fid =(SELECT pid FROM `user` WHERE uid = rootId); 
	IF fid is not null THEN 
		SET str = concat(str, ',', fid); 
		SET rootId = fid; 
	ELSE 
		SET rootId = fid; 
	END IF; 
END WHILE; 
return str;
END$
DELIMITER ;

 

查询代码:

select getParentList('001001001001001'); 
select * from sbkfwh where FIND_IN_SET(id,getParentList('001001001001002')) ;

 

二、查子集合

Sql代码:

DELIMITER $
CREATE DEFINER=`root`@`%` FUNCTION `getChildList`(`rootId` LongText) RETURNS longtext CHARSET utf8mb4
BEGIN 
	DECLARE `str` LongText default '';
	DECLARE `cid` LongText default `rootId`; 
	SET str = '';
	SET cid = rootId; 
	WHILE cid is not null DO 
		SET str = concat(str, ',', cid); 
		SELECT group_concat(uid) INTO cid FROM `user` where FIND_IN_SET(pid, cid) > 0; 
	END WHILE; 
	RETURN str;
END$
DELIMITER ;

 

查询代码:

select getParentList('001001001'); 
select * from sbkfwh where FIND_IN_SET(id,getChildList('001001001'));

 

Mysql函数中并不支持动态sql,Dynamic SQL is not allowed in stored function or trigger

要想查多个表的,可以建多个函数,或用以下方法:

drop FUNCTION `getChildListTest`   
CREATE FUNCTION `getChildListTest`(tableName varchar(64),rootId varchar(100))    
RETURNS varchar(2000)   
BEGIN    
DECLARE str varchar(2000);   
DECLARE cid varchar(100);    
SET str = '
;    
SET cid = rootId;    
 
IF tableName = 'tableName1' THEN  
		WHILE cid is not null DO    
				SET str = concat(str, ',', cid);    
				SELECT group_concat(id) INTO cid FROM tableName1 where FIND_IN_SET(parentid, cid) > 0;   
		END WHILE;  
ELSEIF tableName = 'tableName2' THEN  
		WHILE cid is not null DO    
				SET str = concat(str, ',', cid);    
				SELECT group_concat(id) INTO cid FROM tableName2 where FIND_IN_SET(parentid, cid) > 0;   
		END WHILE;  
END IF;  
 
RETURN str;    
END

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注