mysql8实现oracle的connect by order by实现深度优先的树排序

with recursive tab1 as (
--此处是定义了一个临时sql快,旨在将原始表里按照排序字段排好后,连接@rownum固定下来
with temp_b as (select a.* from (select * from etc_test order by ordernum asc) a,(SELECT @rownum:=0) b)
--以下是连接起始数据和union all连接体
select t0.id,t0.parent_id, 1 lv, @rownum := @rownum + 1 rn, cast(@rownum as char) order_str from temp_b t0 where t0.parent_id is null  
 union all
select t2.id, t2.parent_id, t1.lv + 1, @rownum := @rownum + 1 rn, concat(t1.order_str, '-', @rownum) order_str 
  from temp_b t2, tab1 t1  
 where t2.parent_id = t1.id
)
select t1.*
--显示树深度(可有可无)
,concat(lpad('-', (lv - 1) * 2, '-'), t1.id) tree
  from tab1 t1
 order by 
 --第一顺序必须是取第一个数字进行排序,因为多个根节点的顺序
 --如果是9会排在10后面,(因为按照-连接后的字符串不再是数字)
		 cast((case when instr(t1.order_str,'-')<=0 then t1.order_str else substr(t1.order_str,1,instr(t1.order_str,'-')-1) end) as UNSIGNED INTEGER),
 --第二顺序才是9-1,9-1-1,9-1-2,9-2这类排序
		 t1.order_str
;

本文参考了以下文章,是对其中深度排序的优化,
mysql8 实现connect by语法

本文的实现额外考虑并解决了以下两个实际问题
1.排序字段不一定是Integer类型,可能是任意字符
2.多个起始记录的初始排序会在最后出现按字符排序10排在9前面的问题