MySQL中存储为双向链表的表如何按prev_id、next_id定义顺序查询

方案1：MySQL 8.0及以上版本（递归CTE实现，逻辑清晰性能最优） #

利用MySQL 8.0支持的递归公用表达式，从链表头节点开始逐层遍历生成排序序号，最终按序号输出即可：

WITH RECURSIVE linked_list AS (
    -- 取链表首节点（prev_id为0的记录）作为递归起点
    SELECT id, prev_id, next_id, title, 1 AS sort_seq
    FROM tmp
    WHERE prev_id = 0
    UNION ALL
    -- 递归匹配下一条节点，排序序号逐次+1
    SELECT t.id, t.prev_id, t.next_id, t.title, ll.sort_seq + 1
    FROM tmp t
    INNER JOIN linked_list ll ON t.prev_id = ll.id
)
SELECT id, prev_id, next_id, title
FROM linked_list
ORDER BY sort_seq ASC;

用提供的测试数据执行上述SQL，输出顺序会严格按照链表定义返回：1、2、3、10、5、6，和title的序号完全匹配。

方案2：MySQL 5.x版本（用户变量实现，兼容旧版本） #

如果使用不支持CTE的旧版本MySQL，可以通过用户变量遍历生成排序序号实现：

SELECT id, prev_id, next_id, title
FROM (
    SELECT 
        t.*,
        @sort_seq := @sort_seq + 1 AS sort_seq
    FROM tmp t
    JOIN (
        -- 初始化变量：@current_id为链表首节点ID，@sort_seq初始排序序号为0
        SELECT @current_id := (SELECT id FROM tmp WHERE prev_id = 0), @sort_seq := 0
    ) AS init
    WHERE id = @current_id AND (@current_id := next_id) IS NOT NULL
) AS ordered_list
ORDER BY sort_seq ASC;

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