MySQL 遍历树结构。并拼接成字符串

假设有这样一棵树
要得到这样的结果
id 所有子路径
1 1,2,3,4,11,5,6,7,8,9,10,12
2 2,5,6
3 3,7,8
4 4,9,10,12
5 5
6 6
7 7
8 8
9 9
10 10
12 12
11 11

通过SQL该怎么实现呢

创建测试用例

create table test_1
(id int,
parid int);

insert into test_1 values
(1,0),(2,1),(3,1),(4,1),(5,2),(6,2),(7,3),(8,3),(9,4),(10,4),(12,4),(11,1);

实现语句

select a.id,t.ids ,@a :='' from  test_1 as a 
left join lateral
(
  with RECURSIVE cte as
  (select test_1.* from test_1 where id = a.id
   union all 
   select t1.* from test_1 t1  inner join cte t2 on t1.parid = t2.id),
   cte1 as
   (select @a := concat(@a,id,',')as ids,@b:= @b +1 as roworder from cte,(select @a :='',@b := 0) as tt)
   select  ids from cte1 order by roworder desc limit 1
) as t on 1=1

执行结果

整体思路是用横向派生表 再用公共表达式递归生成每棵树。最后再变成字符串。