mysql分层的其他几种方式实现

接一篇mysql分层方式的实现：https://blog.csdn.net/weixin_41561946/article/details/107132338

分层实现方式

方式一：SQL语句方式
select id,nodename,pid
,(select pid from treenodes where id=t.pid) parentid2
,(select pid from treenodes where id=(select pid from treenodes where id=t.pid)) parentid3
,(select pid from treenodes where id=(select pid from treenodes where id=(select pid from treenodes where id=t.pid))) parentid4
from treenodes t

方式二：存储过程+临时表

create PROCEDURE test(in rootid int,in ndepth int)
begin
			declare v_done int default 0;
			declare v_id int;
			declare cur1 cursor for select id from treenodes where pid=rootid;
			declare continue handler for not found set v_done=1;
			
			insert into tmplist value (null,rootid,ndepth);
			
			open cur1;
			fetch cur1 into v_id;
			while v_done=0 do
				call test(v_id,ndepth+1);
			end while ;
			close cur1;		

end;

create procedure showchildlist(in rootid int)
begin
	create temporary table if not exists tmplist(sno int primary key auto_increment,id int,depth int);
    delete from tmplist;
	call test(rootid,0);
	select a.*,b.* from tmplist a,treenodes b where a.id=b.id order by a.sno;
end

call showchildlist(6);

总结：

1、对于只有固定几层的来讲，第一种实现方式是通用的
2、对于只有多层的情况，第二种方式比较适合，但是受max_sp_recursion_depth=255参数的限制，最大为255层，但对第二种方式，测试表中只有20条记录，没有查询出结果