递归查询中如何按变量排序并返回正确顺序？

先看你的测试场景，你想用递归CTE遍历父子表，同时用@count变量生成递增序号来排序，但结果不符合预期，还会出现顺序不稳定的情况，下面逐个解答你的疑问：

1. 为什么按@count排序会出错？有没有不用新增列的解决办法？ #

问题出在用户变量的赋值时机和递归CTE的执行顺序不匹配。MySQL的用户变量是逐行计算的，但递归CTE的UNION ALL递归部分，MySQL并没有保证固定的行遍历顺序——它可能会根据执行计划、缓存情况，甚至表数据的物理存储顺序来选择遍历顺序。

你原来的写法是在递归过程中直接给@count赋值，这意味着@count的递增顺序完全依赖MySQL遍历子节点的顺序，而这个顺序不是你能控制的。之后再用@count排序，自然得不到预期的顺序。

如果不想新增专门的排序列，可以调整逻辑：先确保递归结果按你想要的顺序（比如广度优先/深度优先）排列，再在这个有序结果上赋值变量。比如：

SET @count = 0;
WITH RECURSIVE cte AS (
 SELECT id, val, parent, CAST(id AS CHAR(255)) AS path
 FROM test WHERE id = 1
 UNION ALL
 SELECT b.id, b.val, b.parent, CONCAT(cte.path, '-', b.id)
 FROM test b INNER JOIN cte ON b.parent = cte.id
)
SELECT id, val, parent, @count := @count + 1 AS count
FROM cte
ORDER BY path; -- 先按路径排序，再赋值变量

这里的path是拼接的父节点到当前节点的ID路径，比如1-2-4，按它排序能保证稳定的深度优先顺序，之后再给@count赋值，此时count的顺序就完全符合预期，再按count排序也不会出错。

2. 为什么按parent排序稳定，按@count排序却不稳定且不符合预期？ #

• 按parent排序是基于表中已有的固定列值，MySQL会严格按照parent的数值大小来排序，所以结果是固定的1-1-1-2-3（所有parent=1的行排前面，然后是parent=2、parent=3）。
• 而@count的赋值是在递归遍历过程中完成的，MySQL每次执行递归查询时，遍历子节点的顺序可能不一样——比如第一次运行先遍历id=2的子节点，第二次可能先遍历id=3的子节点，这就导致@count的赋值顺序随机，对应的count值也跟着变，最终排序结果每次都不同。

你想要的1-2-3-4-5…是深度优先或广度优先的遍历顺序，但@count没有和固定的遍历规则绑定，自然无法得到稳定的递增序列。

3. 还有哪些方法可以生成自定义排序的不存在值？ #

除了用户变量，还有几种更可靠的方式：

• 路径字符串排序：就像上面例子里的path，用CONCAT拼接父节点到当前节点的ID链，按这个字符串排序可以实现稳定的深度优先/广度优先顺序，不需要额外变量。
• 窗口函数生成序号：用ROW_NUMBER()窗口函数，在递归CTE的结果上按指定规则生成排序序号，比如：

  WITH RECURSIVE cte AS (
   SELECT id, val, parent, CAST(id AS CHAR(255)) AS path
   FROM test WHERE id = 1
   UNION ALL
   SELECT b.id, b.val, b.parent, CONCAT(cte.path, '-', b.id)
   FROM test b INNER JOIN cte ON b.parent = cte.id
  )
  SELECT id, val, parent, ROW_NUMBER() OVER(ORDER BY path) AS sort_key
  FROM cte;
  

  这里的sort_key就是生成的自定义排序值，完全符合你的遍历顺序。
• 层级+ID排序：如果是广度优先，可以用level（递归时记录的层级）加id来排序，比如ORDER BY level, id，也能生成稳定的顺序。

这些方法都比用户变量更可靠，因为它们依赖固定的排序规则，而不是MySQL不确定的遍历顺序。

内容的提问来源于stack exchange，提问作者RDU