MySQL视图中能否使用递归语句？评论表层级回复数统计需求

Great question! Let's break this down step by step.

First, to answer your core question: MySQL 8.0 and later fully support recursive Common Table Expressions (CTEs) within views, which is exactly what you need to count all nested replies (direct and indirect) for each comment.

Here's how to implement this: #

First, let's assume your comments table has a structure like this (adjust if your schema differs):

CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    commentOnComment INT NULL, -- References the parent comment's ID (NULL for top-level comments)
    content TEXT,
    -- Add any other fields you have, like created_at, author_id, etc.
    FOREIGN KEY (commentOnComment) REFERENCES comments(id)
);

We'll create a view that uses a recursive CTE to traverse the entire comment hierarchy and count all descendants for each root comment:

CREATE VIEW comments_with_total_replies AS
WITH RECURSIVE comment_hierarchy AS (
    -- Anchor member: Start with every comment as its own root
    SELECT 
        id AS root_id,
        id AS current_id
    FROM comments
    
    UNION ALL
    
    -- Recursive member: Traverse down the hierarchy, linking child comments to their root parent
    SELECT 
        ch.root_id,
        c.id AS current_id
    FROM comment_hierarchy ch
    JOIN comments c ON ch.current_id = c.commentOnComment
)
-- Join the hierarchy data back to the original comments table to get the final count
SELECT 
    c.id,
    c.content,
    c.commentOnComment,
    -- Subtract 1 because the anchor member includes the root comment itself; we only want its replies
    COALESCE(COUNT(ch.current_id) - 1, 0) AS replies
FROM comments c
LEFT JOIN comment_hierarchy ch ON c.id = ch.root_id
GROUP BY c.id, c.content, c.commentOnComment;

How this works: #

1. Recursive CTE (comment_hierarchy):
   • The anchor member selects every comment, marking it as both the root_id (the original comment we're counting replies for) and current_id (the current node in the hierarchy).
   • The recursive member repeatedly joins the hierarchy with the comments table to find all child comments of each current_id, keeping the original root_id intact. This builds a full list of every comment and its root ancestor.
2. Final SELECT:
   • We join the original comments table with the hierarchy data, grouping by each comment's ID.
   • COUNT(ch.current_id) - 1 gives the total number of nested replies: the count includes the root comment itself, so we subtract 1 to exclude it. COALESCE ensures comments with no replies show 0 instead of NULL.

For your example where comment ID 1 has 2 direct replies and 1 nested reply, this view will return 3 for the replies field—exactly what you need.

Important Notes: #

• This requires MySQL 8.0 or newer (recursive CTEs were introduced in 8.0; older versions don't support them).
• The view will automatically reflect changes to the comments table (new replies, deleted comments) since it's a dynamic view, not a static snapshot.
• If your comments table has additional fields, just add them to both the SELECT clause and the GROUP BY clause to include them in the view.

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