mysql评论回复

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE replies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    comment_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (comment_id) REFERENCES comments(id)
);

SELECT * FROM replies WHERE comment_id = ?;

WITH RECURSIVE reply_tree AS (
    SELECT id, comment_id, user_id, content, created_at
    FROM replies
    WHERE comment_id = ?
    UNION ALL
    SELECT r.id, r.comment_id, r.user_id, r.content, r.created_at
    FROM replies r
    INNER JOIN reply_tree rt ON r.comment_id = rt.id
)
SELECT * FROM reply_tree;

CREATE INDEX idx_comment_id ON replies(comment_id);
CREATE INDEX idx_id ON replies(id);