如何在MySQL递归查询中实现每层最多5条子评论的层级查询

问题描述 #

我有一张comments表，包含id、parent_id、content、created_at等字段，需要实现以下查询逻辑：

• 先查询parent_id为NULL的根评论，取最新的5条
• 每个选中的根评论的二级子评论最多保留5条，该规则适用于所有更深层级的评论
  目前仅通过临时表实现了根评论的数量限制，需要修改递归查询，让递归过程中每个父评论最多返回5条子评论，同时避免全表扫描。

表结构与测试数据 #

-- 创建表
CREATE TABLE comments (
    id         INT NOT NULL PRIMARY KEY,
    user_id    BIGINT NOT NULL,
    post_id    BIGINT NOT NULL,
    parent_id  INT DEFAULT NULL,
    content    VARCHAR(10000) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_comments_parent FOREIGN KEY (parent_id) 
      REFERENCES comments(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 一级根评论
INSERT INTO comments (id, user_id, post_id, parent_id, content, created_at) VALUES
(1, 101, 1, NULL, 'Root Comment 1', '2025-04-10 10:00:00'),
(2, 102, 1, NULL, 'Root Comment 2', '2025-04-10 10:05:00'),
(3, 103, 1, NULL, 'Root Comment 3', '2025-04-10 10:10:00'),
(4, 104, 1, NULL, 'Root Comment 4', '2025-04-10 10:15:00'),
(5, 105, 1, NULL, 'Root Comment 5', '2025-04-10 10:20:00'),
(18, 105, 1, NULL, 'Root Comment 6', '2025-04-11 10:20:00');

-- 二级评论（属于根评论1、2、3）
INSERT INTO comments (id, user_id, post_id, parent_id, content, created_at) VALUES
(6, 106, 1, 1, 'Second-level Comment 1 (Child of Root 1)', '2025-04-10 10:25:00'),
(7, 107, 1, 1, 'Second-level Comment 2 (Child of Root 1)', '2025-04-10 10:30:00'),
(8, 108, 1, 2, 'Second-level Comment 3 (Child of Root 2)', '2025-04-10 10:35:00'),
(9, 109, 1, 3, 'Second-level Comment 4 (Child of Root 3)', '2025-04-10 10:40:00'),
(10, 110, 1, 3, 'Second-level Comment 5 (Child of Root 3)', '2025-04-10 10:45:00');

-- 三级评论（属于二级评论）
INSERT INTO comments (id, user_id, post_id, parent_id, content, created_at) VALUES
(11, 111, 1, 6, 'Third-level Comment 1 (Child of Second-level 1)', '2025-04-10 10:50:00'),
(12, 112, 1, 6, 'Third-level Comment 2 (Child of Second-level 1)', '2025-04-10 10:55:00'),
(13, 113, 1, 7, 'Third-level Comment 3 (Child of Second-level 2)', '2025-04-10 11:00:00'),
(14, 114, 1, 8, 'Third-level Comment 4 (Child of Second-level 3)', '2025-04-10 11:05:00'),
(15, 115, 1, 9, 'Third-level Comment 5 (Child of Second-level 4)', '2025-04-10 11:10:00');

-- 四级评论（属于三级评论）
INSERT INTO comments (id, user_id, post_id, parent_id, content, created_at) VALUES
(16, 116, 1, 11, 'Fourth-level Comment 1 (Child of Third-level 1)', '2025-04-10 11:15:00'),
(17, 117, 1, 12, 'Fourth-level Comment 2 (Child of Third-level 2)', '2025-04-10 11:20:00');

现有查询语句 #

WITH RECURSIVE comment_tree AS (
    -- 基例：筛选根评论
    SELECT id, user_id, parent_id, content, created_at, 1 AS level
    FROM comments
    WHERE post_id = 1 AND parent_id IS NULL
    AND id IN (SELECT * FROM (
        SELECT id FROM comments 
        WHERE post_id = 1 AND parent_id IS NULL
        ORDER BY created_at DESC
        LIMIT 5)temp_tab)
    
    UNION ALL

    -- 递归例：筛选子评论
    SELECT c.id, c.user_id, c.parent_id, c.content, c.created_at, ct.level + 1 AS level
    FROM comments c
    JOIN comment_tree ct ON c.parent_id = ct.id
    -- 限制递归层级
    WHERE ct.level < 4
)

SELECT * FROM comment_tree
ORDER BY created_at;

解决方案 #

要实现每层父评论最多返回5条子评论，需要在递归步骤中对每个父节点的子评论进行排序并限制数量，同时通过添加索引避免全表扫描。

1. 添加索引优化查询性能 #

为避免全表扫描，创建复合索引加速查询：

CREATE INDEX idx_comments_parent_post_created ON comments (parent_id, post_id, created_at DESC);
CREATE INDEX idx_comments_post_parent_created ON comments (post_id, parent_id, created_at DESC);

这些索引能让数据库快速定位指定post_id和parent_id的评论，并按created_at排序取最新数据。

2. 修改后的递归查询语句 #

WITH RECURSIVE comment_tree AS (
    -- 基例：取最新5条根评论
    SELECT 
        id, user_id, parent_id, content, created_at, 
        1 AS level,
        CAST(id AS VARCHAR(255)) AS path
    FROM comments
    WHERE post_id = 1 AND parent_id IS NULL
    ORDER BY created_at DESC
    LIMIT 5

    UNION ALL

    -- 递归例：对每个父节点取最新5条子评论
    SELECT 
        c.id, c.user_id, c.parent_id, c.content, c.created_at, 
        ct.level + 1 AS level,
        CONCAT(ct.path, ',', c.id) AS path
    FROM comment_tree ct
    -- 用LATERAL JOIN为每个父节点单独筛选子评论
    JOIN LATERAL (
        SELECT id, user_id, parent_id, content, created_at
        FROM comments
        WHERE post_id = 1 AND parent_id = ct.id
        ORDER BY created_at DESC
        LIMIT 5
    ) c ON true
    -- 可选：如需限制最大递归层级，取消注释下面一行
    -- WHERE ct.level < 10
)

SELECT id, user_id, parent_id, content, created_at, level
FROM comment_tree
-- 按路径和创建时间排序，保持树形结构顺序
ORDER BY path, created_at DESC;

关键说明 #

• 使用LATERAL JOIN（PostgreSQL支持，MySQL 8.0+可用LATERAL，SQL Server用CROSS APPLY），为递归中的每个父节点单独筛选最多5条最新子评论，确保所有层级都符合数量限制。
• 基例直接用ORDER BY created_at DESC LIMIT 5替代原有子查询，更简洁高效。
• 添加path字段用于排序，保证结果按评论的层级结构顺序展示（根评论→子评论→孙评论依次排列）。
• 索引的存在让每个LATERAL子查询都能快速定位数据，避免全表扫描。

验证结果 #

执行修改后的查询，会得到：

• 最新的5条根评论（测试数据中是ID18、5、4、3、2）
• 每个根评论下最多5条二级评论
• 每个二级评论下最多5条三级评论，以此类推，所有层级都满足数量限制

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