这在mysql中有点棘手。其基本思想是使用递归cte建立一条到达顶部的路径，然后按路径排序。但是，您希望路径中的每个标识符的长度保持不变，以避免排序问题。而且，mysql不支持数组，所以这一切都必须放到一个字符串中。
所以，我建议你这样做：

with recursive cte as (
      select id, name, code, parent, 
             cast(lpad(id, 4, '0') as char(255)) as path
      from sample
      where parent is null 
      union all
      select s.id, s.name, s.code, s.parent, 
             concat(cte.path, '->', lpad(s.id, 4, '0'))
      from cte join
           sample s 
           on s.parent = cte.code
     )
select *
from cte
order by path;

这是一把小提琴。
注意：这将ID扩展为四个字符。很容易修改。

你应该使用 Recursive Common Table Expression 为了达到这个目的
试试这个：

with recursive cte(id, name, code, parent, key_) as (

select id, name, code, parent, array[id] as key_   
from sample where parent is null

union all

select t1.id, t1.name, t1.code, t1.parent, t2.key_ || t1.id 
from sample t1 
inner join cte t2 on t1.parent=t2.id
)

select id, name, code, parent from cte order by key_

小提琴演示
编辑
根据你的评论细节修改

with recursive cte(id, name, code,parent,key_) as (
select id,name,code,parent,array[code] as key_   from sample where parent is null
  union all

select t1.id,t1.name,t1.code,t1.parent,t2.key_ || t1.parent from sample t1 inner join cte t2 on t1.parent=t2.code
)

select id, name, code,parent from cte order by key_

演示

您需要使用公共表表达式。如果你是cte新手，下面的教程将帮助你理解
https://dzone.com/articles/understanding-recursive-queries-in-postgreshttps://www.postgresqltutorial.com/postgresql-cte/