如何在MySQL中按相邻行pid匹配上一行id的规则排序表数据
单链自关联数据链式排序的MySQL实现方案
需求说明
针对存储为自关联结构的业务表(包含id主键、name名称、pid父节点id三个字段),要求将零散的行数据按照链式关系重新排序:保证相邻两行的关联逻辑为下一行的pid值完全等于上一行的id值,实现从根节点(pid为NULL的行)出发,顺着父子关联遍历完整条链路的线性顺序。
推荐方案(MySQL 8.0+ 支持递归CTE)
这是稳定性最高、逻辑最清晰的实现方式,通过递归公共表达式锚定链的起点,逐层匹配关联子节点,同时记录遍历顺序,最终按顺序字段排序即可:
-- 将self_chain_table替换为实际业务表名 WITH RECURSIVE chain_sort AS ( -- 锚定链起点:pid为NULL的根节点,初始排序层级为0 SELECT id, name, pid, 0 AS sort_level FROM self_chain_table WHERE pid IS NULL UNION ALL -- 递归匹配:取上一层节点id作为当前层的pid匹配值,层级深度+1 SELECT t.id, t.name, t.pid, cs.sort_level + 1 FROM self_chain_table t INNER JOIN chain_sort cs ON t.pid = cs.id ) -- 输出最终链式排序结果 SELECT id, name, pid FROM chain_sort ORDER BY sort_level;
执行上述SQL后,得到的结果完全匹配要求的顺序:
| id | name | pid |
|---|---|---|
| 1 | row1 | NULL |
| 4 | row4 | 1 |
| 5 | row5 | 4 |
| 2 | row2 | 5 |
| 3 | row3 | 2 |
| 6 | row6 | 3 |
兼容方案(MySQL 5.x 无递归CTE支持)
对于旧版本不支持递归语法的场景,可以通过自定义会话变量模拟逐行链式匹配的逻辑实现排序,使用MySQL的NULL安全等于运算符兼容根节点的NULL值匹配:
-- 将self_chain_table替换为实际业务表名 SELECT t.id, t.name, t.pid, @sort_level := @sort_level + 1 AS sort_level FROM ( -- 初始化会话变量:排序层级初始值为-1,上一节点id初始为NULL SELECT @sort_level := -1, @prev_node_id := NULL ) init_vars CROSS JOIN self_chain_table t -- 逐行匹配:每一行的pid值等于上一节点id,第一次迭代时@prev_node_id为NULL,刚好匹配pid为NULL的根节点 WHERE t.pid <=> @prev_node_id -- 匹配成功后,更新上一节点id为当前行的id,供下一次匹配使用 AND @prev_node_id := t.id ORDER BY sort_level;
注意事项
- 两类方案的适用前提都是表内关联数据为无分支的线性单链,不存在同一个
pid对应多个子节点的情况;如果是带分支的树状结构,需要补充分支排序字段适配递归逻辑,避免遍历漏行。 - 自定义变量实现的旧版本兼容方案,受MySQL执行计划、SQL mode配置影响较大,稳定性弱于递归CTE方案,优先建议升级MySQL到8.0以上版本使用标准递归语法。
- 执行排序前可提前做数据合法性校验:执行
SELECT pid, COUNT(*) AS row_cnt FROM self_chain_table GROUP BY pid HAVING row_cnt >1,检查是否存在多子节点的分支,避免排序时出现漏行、错序问题。
内容的提问来源于stack exchange,提问作者a_k
相关产品推荐
相关产品推荐

