MySQL中的递归查询邻接表深度优先

在其上运行此查询的表的 struct 大致如下:

comments_table(PK id, FK reply_to_id, content) 

FK is a self join on itself

它在10.4.27上运行-MariaDB

数据如下所示:

+----+-------------+---------+
| id | reply_to_id | content |
+----+-------------+---------+
| 12 |     NULL    |   text  |
| 13 |      12     |   text  |
| 14 |      12     |   text  |
| 15 |      13     |   text  |
+----+-------------+---------+

该查询应该按顺序检索输入父项(或树根)中给出的所有回复注释.

结果顺序应该是深度优先.

预期结果的一个示例:

Input : 12
Result: 13,15,14

    12
  /    \
13      14
  \
   15
+----+
| id |
|----+
| 13 |
| 15 |
| 14 |
+----+

诸若此类

我try 存档的是在不使用任何外部代码的情况下在查询中完成此操作.

我一直在try 递归并修改如下所示的查询:

select id 
from (
    select * from comments order by id
) comments_sorted, (
    select @pv := '62'
) initialisation 
where find_in_set(replied_to_id, @pv)
and length(@pv := concat(@pv, ',', id));

query does work，它在输出中给出对给定父亲(或树根)的所有回复

输出如下所示:

+----+
| id |
+----+
| 13 |
| 14 |
| 15 |
+----+

同时，所需的输出如上图所示

它怎么可能实现呢？

EDIT个

提供其他反馈

使用带有以下数据集的查询@luuk:

+----+---------------+
| id | replied_to_id |
+----+---------------+
| 81 |          NULL |
| 82 |          NULL |
| 83 |            82 |
| 84 |            83 |
| 85 |            83 |
| 86 |            83 |
| 87 |            84 |
| 88 |            87 |
| 93 |            88 |
+----+---------------+

我得到的结果是:

+---+----+---------------+
| x | id | replied_to_id |
+---+----+---------------+
| 1 | 83 |            82 |
| 1 | 84 |            83 |
| 1 | 85 |            83 |
| 1 | 86 |            83 |
| 1 | 87 |            84 |
| 1 | 88 |            87 |
| 1 | 93 |            88 |
+---+----+---------------+

我可以看到x值没有递增.

我使用的查询是:

WITH RECURSIVE cte AS ( 
   SELECT row_number() over (order by id) as x, id, replied_to_id 
   FROM comments 
   WHERE replied_to_id=82 
   UNION ALL 
   SELECT x, comments.id, comments.replied_to_id 
   FROM cte 
   INNER JOIN comments on comments.replied_to_id = cte.id 
) 
SELECT * FROM cte ORDER BY x,id;

会是什么呢？