如何实现带分页的递归分类数据查询？

我有一张名为category的表，表结构及数据如下：

需要实现带分页的递归数据查询，比如当limit = 5时，要按层级结构展示树形分页，目前采用先获取全部数据再分页的方式，但数据量较大时性能不佳，求优化方案。

核心思路 #

要实现树形结构的分页，关键是先通过递归查询生成带层级路径、排序键的数据集，再对这个数据集分页，同时保留层级关系。不能直接对递归结果用普通LIMIT，否则会破坏树形结构完整性（比如截断某个父节点的子节点），所以需要先确保分页范围内的树形节点完整，再输出结果。

方案1：MySQL 8.0+（CTE递归实现） #

MySQL 8.0及以上支持CTE公共表表达式，可生成带排序路径的递归数据集后分页：

WITH RECURSIVE category_tree AS (
    -- 根节点：parent_id=0，初始化层级、路径、排序键
    SELECT 
        id, 
        name, 
        parent_id, 
        1 AS level,
        CAST(id AS CHAR(255)) AS path,
        CONCAT(LPAD(id, 4, '0')) AS sort_key -- 补零生成排序键，保证父节点在前、子节点按id排序
    FROM category
    WHERE parent_id = 0
    
    UNION ALL
    
    -- 递归子节点：拼接路径和排序键
    SELECT 
        c.id, 
        c.name, 
        c.parent_id, 
        ct.level + 1 AS level,
        CONCAT(ct.path, ',', c.id) AS path,
        CONCAT(ct.sort_key, ',', LPAD(c.id, 4, '0')) AS sort_key
    FROM category c
    JOIN category_tree ct ON c.parent_id = ct.id
)
-- 按排序键排序后分页，保留层级用于前端缩进
SELECT 
    id,
    name,
    parent_id,
    level,
    path
FROM category_tree
ORDER BY sort_key
LIMIT 0, 5; -- 第一页，每页5条

执行结果（对应limit=5）：

方案2：PostgreSQL（递归CTE + 分页） #

PostgreSQL可使用数组存储路径，排序更便捷：

WITH RECURSIVE category_tree AS (
    SELECT 
        id, 
        name, 
        parent_id, 
        1 AS level,
        ARRAY[id] AS path
    FROM category
    WHERE parent_id = 0
    
    UNION ALL
    
    SELECT 
        c.id, 
        c.name, 
        c.parent_id, 
        ct.level + 1 AS level,
        ct.path || c.id AS path
    FROM category c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT 
    id,
    name,
    parent_id,
    level,
    path
FROM category_tree
ORDER BY path
LIMIT 5 OFFSET 0;

方案3：SQL Server（递归CTE + 分页） #

SQL Server用OFFSET ... FETCH NEXT实现分页逻辑：

WITH RECURSIVE category_tree AS (
    SELECT 
        id, 
        name, 
        parent_id, 
        1 AS level,
        CAST('/' + CAST(id AS VARCHAR(20)) + '/' AS VARCHAR(MAX)) AS path
    FROM category
    WHERE parent_id = 0
    
    UNION ALL
    
    SELECT 
        c.id, 
        c.name, 
        c.parent_id, 
        ct.level + 1 AS level,
        ct.path + CAST(c.id AS VARCHAR(20)) + '/' AS path
    FROM category c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT 
    id,
    name,
    parent_id,
    level,
    path
FROM category_tree
ORDER BY path
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

注意事项 #

1. 索引优化：给parent_id字段建立索引，可大幅提升递归查询的性能。
2. 分页场景适配：如果需要按「每页展示N个根节点及其所有子节点」分页，需先分页查询根节点，再递归查询每个根节点的子节点，逻辑需调整。
3. 前端展示：可根据level字段实现层级缩进，比如level=1无缩进，level=2缩进20px等。

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