Echo Server：评论、点赞与统计聚合

第四阶段：从“能发帖”升级到“能互动”，并让列表直接返回统计数据

评论点赞SQL 聚合

0. 总体路线

先建立 comments 表，再建立 post_likes 表，最后把 like_count 和 comment_count 聚合进帖子列表接口。

项目	说明
建议位置	精选作品详情页 / 项目文档页
统一风格	左侧导航、右侧正文、表格说明、折叠代码块、阶段总结。
阅读顺序	建议从总览页进入，再按卡片顺序阅读。

1. 评论系统

评论需要关联 posts 和 users，删除帖子或用户时应级联删除评论。

CREATE TABLE IF NOT EXISTS comments (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  post_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_comments_post_id (post_id),
  INDEX idx_comments_user_id (user_id),
  CONSTRAINT fk_comments_post_id FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  CONSTRAINT fk_comments_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 点赞系统

点赞表必须使用 (post_id, user_id) 唯一索引，避免同一个用户重复点赞。

CREATE TABLE IF NOT EXISTS post_likes (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  post_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_post_user (post_id, user_id),
  INDEX idx_likes_post_id (post_id),
  INDEX idx_likes_user_id (user_id),
  CONSTRAINT fk_likes_post_id FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  CONSTRAINT fk_likes_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. 统计聚合

列表接口要一次返回前端首页需要的核心字段，避免前端对每条帖子继续请求点赞数和评论数。

SELECT
  p.id,
  p.user_id,
  u.username,
  COALESCE(u.nickname, '') AS nickname,
  COALESCE(u.avatar_url, '') AS avatar_url,
  p.content,
  COALESCE(p.image_url, '') AS image_url,
  DATE_FORMAT(p.created_at, '%Y-%m-%d %H:%i:%s') AS created_at,
  COALESCE(lc.like_count, 0) AS like_count,
  COALESCE(cc.comment_count, 0) AS comment_count
FROM posts p
JOIN users u ON u.id = p.user_id
LEFT JOIN (
  SELECT post_id, COUNT(*) AS like_count
  FROM post_likes
  GROUP BY post_id
) lc ON lc.post_id = p.id
LEFT JOIN (
  SELECT post_id, COUNT(*) AS comment_count
  FROM comments
  GROUP BY post_id
) cc ON cc.post_id = p.id
ORDER BY p.id DESC
LIMIT ? OFFSET ?;

4. 完成标准

点赞后 like_count 增加，取消点赞后减少；评论后 comment_count 增加，删除评论后减少。