mysql 父子节点查询

table需要改名字
表结构
table (Id int,Name varchar(100),ParentId int)

依据父查子

drop function if exists GetChilds;
create function GetChilds(rootid int)
returns varchar(1000)
 
begin 
	declare stmp varchar(1000);
	declare stmpchild  varchar(1000);
	declare tid  int ;
	set stmp = ''; -- $
	set stmpchild = cast(rootid as char);
	
	while stmpchild is not null do 
		select id into tid from table where id = rootid;
		if tid > -1 then
				set stmp = concat(stmp,',',stmpchild);
					select group_concat(id order by id asc separator ',') into stmpchild from table  where find_in_set(ParentId,stmpchild);
				else 
					return SUBSTR(stmp,2); -- stmp
		end if;
	end while ;
	return substr(stmp,2); -- stmp 
end 

SELECT getchilds(1441);
SELECT getchilds(1471);
SELECT getchilds(10000000000);

查找字符在集合中的位置

select find_in_set('a','a,b,c,d'); -- 1
select find_in_set('c','a,b,c,d'); -- 3 
select find_in_set('x','a,b,c,d');-- 0 
select find_in_set('x','');-- 0
select find_in_set('x',null);-- Null

依据子查父

drop function if exists GetParents;
create function GetParents(rootid int)
returns varchar(1000)
 
begin 
	declare stmp varchar(1000);
	declare stmpparent  varchar(1000);
	declare tid  int ;
	set stmp = ''; -- $
	set stmpparent = cast(rootid as char);
	
	while stmpparent is not null do 
		select ParentId into tid from table where id = rootid;
		if tid > -1 then
				set stmp = concat(stmp,',',stmpparent);
					select group_concat(ParentId) into stmpparent from table where find_in_set(id,stmpparent)>0 and ParentId>0;
				else 
					return substr(stmp,2); -- stmp
		end if;
	end while ;
	return substr(stmp,2); -- stmp 
end 

SELECT GetParents(1471);
SELECT GetParents(1441);
SELECT GetParents(14711471);