MySQL查询特定评论所属的全层级评论链（含主帖）

嘿，我来帮你搞定这个从特定评论回溯到主帖的层级查询需求！根据你给出的表结构和数据，这里有两种靠谱的实现方案，适配不同版本的MySQL：

方案一：用递归CTE（MySQL 8.0+推荐） #

如果你的MySQL是8.0或更高版本，递归CTE绝对是最简洁高效的选择，直接写一个递归查询就能拉通整个评论链：

WITH RECURSIVE comment_chain AS (
    -- 第一步：先选中我们要查询的目标评论（这里是id=30）
    SELECT id_post, post, comment_of, 0 AS depth
    FROM posts
    WHERE id_post = 30
    UNION ALL
    -- 第二步：递归向上找父评论，直到摸到主帖（comment_of=0）
    SELECT p.id_post, p.post, p.comment_of, cc.depth + 1 AS depth
    FROM posts p
    JOIN comment_chain cc ON p.id_post = cc.comment_of
    WHERE p.comment_of != 0
)
-- 按层级从主帖到目标评论排序，或者反过来都可以
SELECT id_post, post, comment_of, depth
FROM comment_chain
ORDER BY depth DESC;

为啥这么写？ #

• comment_chain是我们定义的递归临时表，分两部分：
  • 锚点查询：先定位到目标评论（id=30），给它标记depth=0作为链条起点。
  • 递归查询：通过关联当前链条里的comment_of，把父评论拉进来，每往上一层depth加1，直到父评论是主帖（comment_of=0）就停止。

对应你的数据示例，输出会是这样： #

要是想从目标评论往主帖的顺序展示，把ORDER BY depth DESC改成ORDER BY depth ASC就行。

方案二：兼容MySQL 5.x的存储过程 #

如果你的MySQL版本还停留在5.x（不支持递归CTE），那用存储过程来循环查询也是个可行的办法：

DELIMITER //
CREATE PROCEDURE GetCommentChain(IN target_id INT)
BEGIN
    DECLARE current_id INT;
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_chain (id_post INT, post TEXT, comment_of INT, depth INT);
    
    SET current_id = target_id;
    SET @depth = 0;
    
    -- 循环向上找父评论，直到找到主帖（comment_of=0）
    WHILE current_id != 0 DO
        INSERT INTO temp_chain
        SELECT id_post, post, comment_of, @depth
        FROM posts
        WHERE id_post = current_id;
        
        SET @depth = @depth + 1;
        SELECT comment_of INTO current_id FROM posts WHERE id_post = current_id;
    END WHILE;
    
    -- 输出结果并清理临时表
    SELECT * FROM temp_chain ORDER BY depth DESC;
    DROP TEMPORARY TABLE IF EXISTS temp_chain;
END //
DELIMITER ;

-- 调用存储过程查询id=30的评论链
CALL GetCommentChain(30);

这个存储过程会一步步向上遍历父评论，把每一层的内容存入临时表，最后统一输出，同样能得到完整的层级链。

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