mysql递归增加层次码

mysql父子关系递归查询增加层次码。

例子：

已有字段id,parentid.

需要增加字段内容：

101

102

10101

10102

10201

10202

1020201

代码：

with recursive my_tree as

(SELECT id,parentid,1 as n,cast('0' as char(50)) tree,cast(concat('1',lpad(@rn:=@rn+1,3,'0')) as char(50)) tree_id

FROM ex,(select @rn:=0) r

WHERE parentid='0' -- :starting_id

UNION ALL

SELECT d.id,d.parentid,n+1,lpad(d.tree,3,'0'),concat(tree_id,lpad(d.tree,3,'0'))

FROM (select id,parentid,ROW_NUMBER()over(PARTITION by parentid) tree from ex ) d

INNER JOIN tree st ON d.parentid = st.id

)

select * from my_tree