如何简化comments表的层级查询？寻求高效SQL替代方案

我有一个comments表，数据如下：

需要查询id=1的评论及其所有子评论、孙评论，期望结果如下：

目前使用的查询语句如下：

SELECT * FROM comments WHERE id = 1
UNION
SELECT * FROM comments WHERE parent_id IN (SELECT id FROM comments WHERE id = 1)
UNION
SELECT * FROM comments WHERE parent_id IN (SELECT id FROM comments WHERE parent_id IN (SELECT id FROM comments WHERE id = 1));

但该方案在需要获取更深层级（如曾孙评论）时会变得混乱且易出错。请问是否有更简洁、性能更优的查询方式？

1. 递归CTE（推荐，适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

递归公共表表达式（CTE）可以一次性遍历任意深度的层级数据，写法简洁且易维护，完全避免多层嵌套子查询的问题：

WITH RECURSIVE comment_tree AS (
    -- 锚点：获取根评论（id=1）
    SELECT id, parent_id, content
    FROM comments
    WHERE id = 1
    UNION ALL
    -- 递归：关联父节点，获取所有子级评论
    SELECT c.id, c.parent_id, c.content
    FROM comments c
    INNER JOIN comment_tree ct ON c.parent_id = ct.id
)
SELECT * FROM comment_tree;

这个查询会自动遍历所有层级的子评论，无论深度如何，都能一次性返回结果。

2. 性能优化要点 #

• 给parent_id字段创建索引：CREATE INDEX idx_comments_parent_id ON comments(parent_id);，能大幅提升递归关联时的查询速度。
• 锚点查询直接过滤根节点，避免全表扫描。

3. 低版本MySQL（<8.0）替代方案 #

如果数据库版本不支持递归CTE，可以用存储过程实现递归查询：

DELIMITER //
CREATE PROCEDURE GetCommentTree(IN root_id INT)
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_comments (id INT, parent_id INT, content VARCHAR(255));
    INSERT INTO temp_comments SELECT id, parent_id, content FROM comments WHERE id = root_id;
    
    WHILE ROW_COUNT() > 0 DO
        INSERT INTO temp_comments
        SELECT c.id, c.parent_id, c.content
        FROM comments c
        LEFT JOIN temp_comments tc ON c.id = tc.id
        WHERE c.parent_id IN (SELECT id FROM temp_comments) AND tc.id IS NULL;
    END WHILE;
    
    SELECT * FROM temp_comments;
    DROP TEMPORARY TABLE IF EXISTS temp_comments;
END //
DELIMITER ;

-- 调用存储过程获取结果
CALL GetCommentTree(1);

不过这种方式不如递归CTE简洁，优先建议升级数据库版本使用递归方案。

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