SQL: 综合项目：博客系统

[users] 1 ──── N [articles] N ──── N [tags]
   │                  │
   │                  │ 1
   │                  │
   │                  N
   │             [comments]
   │                  │
   N                  │ (自引用)
   │                  │
[users] 1 ──── N [comments]

[categories] 1 ──── N [articles]
     │
     └── 1 (自引用，parent_id)

-- =============================================
-- 博客系统数据库建表脚本
-- 字符集: utf8mb4（支持 emoji 和多语言）
-- 存储引擎: InnoDB（支持事务和外键）
-- =============================================

-- 创建数据库
CREATE DATABASE IF NOT EXISTS blog_system
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

USE blog_system;

-- ----------------------------
-- 1. 用户表
-- ----------------------------
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
    nickname VARCHAR(50) COMMENT '昵称',
    avatar_url VARCHAR(500) COMMENT '头像URL',
    bio VARCHAR(500) COMMENT '个人简介',
    website VARCHAR(200) COMMENT '个人网站',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 1-正常 0-禁用 2-待验证',
    role VARCHAR(20) NOT NULL DEFAULT 'user' COMMENT '角色: user/author/admin',
    article_count INT NOT NULL DEFAULT 0 COMMENT '文章数（冗余字段）',
    follower_count INT NOT NULL DEFAULT 0 COMMENT '粉丝数（冗余字段）',
    last_login_at DATETIME COMMENT '最后登录时间',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '软删除: 0-正常 1-已删除',
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- ----------------------------
-- 2. 用户关注关系表（多对多）
-- ----------------------------
CREATE TABLE user_follows (
    follower_id BIGINT NOT NULL COMMENT '关注者ID',
    following_id BIGINT NOT NULL COMMENT '被关注者ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '关注时间',
    PRIMARY KEY (follower_id, following_id),
    INDEX idx_following_id (following_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户关注关系表';

-- ----------------------------
-- 3. 分类表（支持层级分类）
-- ----------------------------
CREATE TABLE categories (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '分类ID',
    name VARCHAR(50) NOT NULL COMMENT '分类名称',
    slug VARCHAR(100) NOT NULL COMMENT 'URL 别名',
    description VARCHAR(200) COMMENT '分类描述',
    parent_id BIGINT DEFAULT NULL COMMENT '父分类ID，NULL表示顶级分类',
    sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
    article_count INT NOT NULL DEFAULT 0 COMMENT '文章数（冗余字段）',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_slug (slug),
    INDEX idx_parent_id (parent_id),
    INDEX idx_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分类表';

-- ----------------------------
-- 4. 标签表
-- ----------------------------
CREATE TABLE tags (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '标签ID',
    name VARCHAR(50) NOT NULL COMMENT '标签名称',
    slug VARCHAR(100) NOT NULL COMMENT 'URL 别名',
    article_count INT NOT NULL DEFAULT 0 COMMENT '文章数（冗余字段）',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_name (name),
    UNIQUE KEY uk_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='标签表';

-- ----------------------------
-- 5. 文章表
-- ----------------------------
CREATE TABLE articles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '文章ID',
    user_id BIGINT NOT NULL COMMENT '作者ID',
    category_id BIGINT COMMENT '分类ID',
    title VARCHAR(200) NOT NULL COMMENT '标题',
    slug VARCHAR(200) NOT NULL COMMENT 'URL 别名',
    summary VARCHAR(500) COMMENT '摘要',
    content LONGTEXT NOT NULL COMMENT '正文内容',
    cover_image_url VARCHAR(500) COMMENT '封面图片',
    status TINYINT NOT NULL DEFAULT 0 COMMENT '状态: 0-草稿 1-已发布 2-已下架',
    is_pinned TINYINT NOT NULL DEFAULT 0 COMMENT '是否置顶: 0-否 1-是',
    view_count INT NOT NULL DEFAULT 0 COMMENT '浏览数',
    like_count INT NOT NULL DEFAULT 0 COMMENT '点赞数',
    comment_count INT NOT NULL DEFAULT 0 COMMENT '评论数（冗余字段）',
    published_at DATETIME COMMENT '发布时间',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '软删除: 0-正常 1-已删除',
    UNIQUE KEY uk_slug (slug),
    INDEX idx_user_id (user_id),
    INDEX idx_category_id (category_id),
    INDEX idx_status_published (status, published_at DESC),
    INDEX idx_is_pinned (is_pinned),
    INDEX idx_created_at (created_at DESC),
    FULLTEXT INDEX ft_title_content (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文章表';

-- ----------------------------
-- 6. 文章-标签关联表（多对多）
-- ----------------------------
CREATE TABLE article_tag (
    article_id BIGINT NOT NULL COMMENT '文章ID',
    tag_id BIGINT NOT NULL COMMENT '标签ID',
    PRIMARY KEY (article_id, tag_id),
    INDEX idx_tag_id (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章标签关联表';

-- ----------------------------
-- 7. 评论表（支持嵌套回复）
-- ----------------------------
CREATE TABLE comments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '评论ID',
    article_id BIGINT NOT NULL COMMENT '文章ID',
    user_id BIGINT NOT NULL COMMENT '评论者ID',
    parent_id BIGINT DEFAULT NULL COMMENT '父评论ID，NULL表示顶级评论',
    reply_to_user_id BIGINT DEFAULT NULL COMMENT '被回复的用户ID',
    content TEXT NOT NULL COMMENT '评论内容',
    like_count INT NOT NULL DEFAULT 0 COMMENT '点赞数',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 0-待审核 1-已通过 2-已拒绝',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '软删除',
    INDEX idx_article_id (article_id),
    INDEX idx_user_id (user_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_created_at (created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评论表';

-- ----------------------------
-- 8. 文章点赞表
-- ----------------------------
CREATE TABLE article_likes (
    user_id BIGINT NOT NULL COMMENT '用户ID',
    article_id BIGINT NOT NULL COMMENT '文章ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, article_id),
    INDEX idx_article_id (article_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章点赞表';

-- 注册新用户
INSERT INTO users (username, email, password_hash, nickname)
VALUES ('zhangsan', 'zhangsan@example.com', SHA2('mypassword123', 256), '张三');

-- 用户登录（根据用户名查询）
SELECT id, username, nickname, avatar_url, role
FROM users
WHERE username = 'zhangsan' AND password_hash = SHA2('mypassword123', 256)
    AND status = 1 AND is_deleted = 0;

-- 更新个人资料
UPDATE users
SET nickname = '张三丰',
    bio = '热爱编程，热爱生活',
    website = 'https://zhangsan.dev'
WHERE id = 1 AND is_deleted = 0;

-- 软删除用户
UPDATE users SET is_deleted = 1 WHERE id = 1;

-- 查询用户详情（排除已删除）
SELECT id, username, email, nickname, avatar_url, bio, website,
       article_count, follower_count, created_at
FROM users
WHERE id = 1 AND is_deleted = 0;

-- 创建顶级分类
INSERT INTO categories (name, slug, description, sort_order)
VALUES ('技术', 'tech', '技术相关文章', 1),
       ('生活', 'life', '生活随笔', 2),
       ('读书', 'reading', '读书笔记', 3);

-- 创建子分类
INSERT INTO categories (name, slug, description, parent_id, sort_order)
VALUES ('前端', 'frontend', '前端开发技术', 1, 1),
       ('后端', 'backend', '后端开发技术', 1, 2),
       ('数据库', 'database', '数据库技术', 1, 3),
       ('JavaScript', 'javascript', 'JavaScript 语言', 4, 1),
       ('Python', 'python', 'Python 语言', 5, 1);

-- 查询分类树
SELECT
    c1.id, c1.name AS level1,
    c2.name AS level2,
    c3.name AS level3
FROM categories c1
LEFT JOIN categories c2 ON c2.parent_id = c1.id
LEFT JOIN categories c3 ON c3.parent_id = c2.id
WHERE c1.parent_id IS NULL
ORDER BY c1.sort_order, c2.sort_order, c3.sort_order;

-- 创建标签
INSERT INTO tags (name, slug) VALUES
    ('MySQL', 'mysql'),
    ('JavaScript', 'javascript'),
    ('Python', 'python'),
    ('Docker', 'docker'),
    ('Redis', 'redis'),
    ('Vue.js', 'vuejs'),
    ('React', 'react'),
    ('算法', 'algorithms');

-- 查询标签（按文章数排序）
SELECT t.id, t.name, t.slug, t.article_count
FROM tags t
ORDER BY t.article_count DESC
LIMIT 20;

-- 创建文章（草稿）
INSERT INTO articles (user_id, category_id, title, slug, summary, content, status)
VALUES (
    1,
    7,  -- JavaScript 分类
    'MySQL索引优化实战指南',
    'mysql-index-optimization-guide',
    '本文详细讲解MySQL索引优化的核心策略和实战技巧',
    '# MySQL索引优化\n\n## 什么是索引\n\n索引是...',
    0  -- 草稿
);

-- 发布文章
UPDATE articles
SET status = 1,
    published_at = NOW()
WHERE id = 1 AND user_id = 1;

-- 给文章打标签
INSERT INTO article_tag (article_id, tag_id) VALUES
    (1, 1),  -- MySQL
    (1, 8);  -- 算法

-- 更新文章标签（先删后插）
DELETE FROM article_tag WHERE article_id = 1;
INSERT INTO article_tag (article_id, tag_id) VALUES
    (1, 1),
    (1, 5);  -- MySQL + Redis

-- 软删除文章
UPDATE articles SET is_deleted = 1 WHERE id = 1 AND user_id = 1;

-- 更新冗余字段：用户文章数
UPDATE users SET article_count = (
    SELECT COUNT(*) FROM articles WHERE user_id = 1 AND is_deleted = 0
) WHERE id = 1;

-- 更新冗余字段：分类文章数
UPDATE categories SET article_count = (
    SELECT COUNT(*) FROM articles WHERE category_id = 7 AND status = 1 AND is_deleted = 0
) WHERE id = 7;

-- 更新冗余字段：标签文章数
UPDATE tags SET article_count = (
    SELECT COUNT(*) FROM article_tag WHERE tag_id = 1
) WHERE id = 1;

-- 发表评论
INSERT INTO comments (article_id, user_id, content)
VALUES (1, 2, '这篇文章写得很好，学到了很多！');

-- 回复评论
INSERT INTO comments (article_id, user_id, parent_id, reply_to_user_id, content)
VALUES (1, 3, 1, 2, '我也觉得，特别是索引那部分讲得很清楚');

-- 更新文章评论数
UPDATE articles SET comment_count = (
    SELECT COUNT(*) FROM comments WHERE article_id = 1 AND status = 1 AND is_deleted = 0
) WHERE id = 1;

-- 软删除评论
UPDATE comments SET is_deleted = 1 WHERE id = 1 AND user_id = 2;

-- 关注用户
INSERT INTO user_follows (follower_id, following_id) VALUES (2, 1);
-- 更新粉丝数冗余字段
UPDATE users SET follower_count = follower_count + 1 WHERE id = 1;

-- 取消关注
DELETE FROM user_follows WHERE follower_id = 2 AND following_id = 1;
UPDATE users SET follower_count = follower_count - 1 WHERE id = 1;

-- 查询我的关注列表
SELECT u.id, u.username, u.nickname, u.avatar_url, u.bio
FROM user_follows f
JOIN users u ON f.following_id = u.id
WHERE f.follower_id = 2 AND u.is_deleted = 0
ORDER BY f.created_at DESC;

-- 查询我的粉丝列表
SELECT u.id, u.username, u.nickname, u.avatar_url, u.bio
FROM user_follows f
JOIN users u ON f.follower_id = u.id
WHERE f.following_id = 1 AND u.is_deleted = 0
ORDER BY f.created_at DESC;

SELECT
    a.id,
    a.title,
    a.slug,
    a.view_count,
    a.like_count,
    a.comment_count,
    a.published_at,
    u.username AS author,
    u.avatar_url AS author_avatar,
    c.name AS category_name,
    -- 综合热度分：浏览量×1 + 点赞数×5 + 评论数×3
    (a.view_count * 1 + a.like_count * 5 + a.comment_count * 3) AS hot_score,
    GROUP_CONCAT(t.name ORDER BY t.name SEPARATOR ', ') AS tags
FROM articles a
JOIN users u ON a.user_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
LEFT JOIN article_tag at2 ON a.id = at2.article_id
LEFT JOIN tags t ON at2.tag_id = t.id
WHERE a.status = 1
    AND a.is_deleted = 0
    AND a.published_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY a.id
ORDER BY hot_score DESC
LIMIT 20;

SELECT
    u.id,
    u.username,
    u.nickname,
    u.article_count,
    u.follower_count,
    -- 总浏览量
    IFNULL(SUM(a.view_count), 0) AS total_views,
    -- 总点赞数
    IFNULL(SUM(a.like_count), 0) AS total_likes,
    -- 总评论数
    IFNULL(SUM(a.comment_count), 0) AS total_comments,
    -- 最近发布文章时间
    MAX(a.published_at) AS last_published_at,
    -- 平均每篇文章浏览量
    IFNULL(ROUND(AVG(a.view_count), 0), 0) AS avg_views_per_article
FROM users u
LEFT JOIN articles a ON u.id = a.user_id AND a.status = 1 AND a.is_deleted = 0
WHERE u.id = 1 AND u.is_deleted = 0
GROUP BY u.id;

SELECT
    a.id,
    a.title,
    a.content,
    a.cover_image_url,
    a.view_count,
    a.like_count,
    a.comment_count,
    a.published_at,
    a.updated_at,
    u.id AS author_id,
    u.username AS author_username,
    u.nickname AS author_nickname,
    u.avatar_url AS author_avatar,
    u.bio AS author_bio,
    c.name AS category_name,
    c.slug AS category_slug
FROM articles a
JOIN users u ON a.user_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
WHERE a.slug = 'mysql-index-optimization-guide'
    AND a.status = 1
    AND a.is_deleted = 0;

-- 查询文章标签
SELECT t.id, t.name, t.slug
FROM article_tag at2
JOIN tags t ON at2.tag_id = t.id
WHERE at2.article_id = 1;

-- 增加浏览量
UPDATE articles SET view_count = view_count + 1 WHERE id = 1;

-- 查询顶级评论
SELECT
    c.id,
    c.content,
    c.like_count,
    c.created_at,
    u.id AS user_id,
    u.username,
    u.nickname,
    u.avatar_url
FROM comments c
JOIN users u ON c.user_id = u.id
WHERE c.article_id = 1
    AND c.parent_id IS NULL
    AND c.status = 1
    AND c.is_deleted = 0
ORDER BY c.created_at DESC
LIMIT 20;

-- 查询某条评论的回复
SELECT
    c.id,
    c.content,
    c.like_count,
    c.created_at,
    u.username,
    u.nickname,
    u.avatar_url,
    ru.username AS reply_to_username,
    ru.nickname AS reply_to_nickname
FROM comments c
JOIN users u ON c.user_id = u.id
LEFT JOIN users ru ON c.reply_to_user_id = ru.id
WHERE c.parent_id = 1
    AND c.status = 1
    AND c.is_deleted = 0
ORDER BY c.created_at ASC;

-- 查询某个标签下的所有文章
SELECT
    a.id,
    a.title,
    a.slug,
    a.summary,
    a.view_count,
    a.like_count,
    a.published_at,
    u.username AS author,
    u.avatar_url AS author_avatar
FROM article_tag at2
JOIN articles a ON at2.article_id = a.id
JOIN users u ON a.user_id = u.id
WHERE at2.tag_id = 1
    AND a.status = 1
    AND a.is_deleted = 0
ORDER BY a.published_at DESC
LIMIT 20;

-- 各分类文章数量和平均浏览量
SELECT
    c.id,
    c.name,
    c.slug,
    c.article_count,
    IFNULL(COUNT(a.id), 0) AS actual_count,
    IFNULL(ROUND(AVG(a.view_count)), 0) AS avg_views,
    IFNULL(MAX(a.published_at), NULL) AS latest_article_at
FROM categories c
LEFT JOIN articles a ON c.id = a.category_id
    AND a.status = 1 AND a.is_deleted = 0
GROUP BY c.id
ORDER BY c.sort_order;

-- 查询某分类及其所有子分类的文章数
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = 1  -- 技术分类
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    ct.id,
    ct.name,
    COUNT(a.id) AS article_count
FROM category_tree ct
LEFT JOIN articles a ON a.category_id = ct.id AND a.status = 1 AND a.is_deleted = 0
GROUP BY ct.id, ct.name;

-- 查询我关注的用户发布的最新文章
SELECT
    a.id,
    a.title,
    a.slug,
    a.summary,
    a.view_count,
    a.like_count,
    a.published_at,
    u.username AS author,
    u.nickname AS author_nickname,
    u.avatar_url AS author_avatar
FROM user_follows f
JOIN articles a ON f.following_id = a.user_id
JOIN users u ON a.user_id = u.id
WHERE f.follower_id = 2
    AND a.status = 1
    AND a.is_deleted = 0
ORDER BY a.published_at DESC
LIMIT 20;

-- 已发布文章视图（常用，简化查询）
CREATE VIEW v_published_articles AS
SELECT
    a.id,
    a.user_id,
    a.category_id,
    a.title,
    a.slug,
    a.summary,
    a.cover_image_url,
    a.status,
    a.is_pinned,
    a.view_count,
    a.like_count,
    a.comment_count,
    a.published_at,
    a.created_at,
    u.username AS author_username,
    u.nickname AS author_nickname,
    u.avatar_url AS author_avatar,
    c.name AS category_name,
    c.slug AS category_slug
FROM articles a
JOIN users u ON a.user_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
WHERE a.status = 1 AND a.is_deleted = 0;

-- 使用视图查询
SELECT * FROM v_published_articles ORDER BY published_at DESC LIMIT 20;
SELECT * FROM v_published_articles WHERE category_id = 1 ORDER BY view_count DESC;

-- 文章标签汇总视图
CREATE VIEW v_article_tags AS
SELECT
    at2.article_id,
    GROUP_CONCAT(t.name ORDER BY t.name SEPARATOR ', ') AS tag_names,
    GROUP_CONCAT(t.slug ORDER BY t.name SEPARATOR ', ') AS tag_slugs,
    COUNT(t.id) AS tag_count
FROM article_tag at2
JOIN tags t ON at2.tag_id = t.id
GROUP BY at2.article_id;

-- 带标签的文章查询
SELECT
    vpa.*,
    vat.tag_names,
    vat.tag_count
FROM v_published_articles vpa
LEFT JOIN v_article_tags vat ON vpa.id = vat.article_id
ORDER BY vpa.published_at DESC
LIMIT 20;

-- 1. 文章列表查询优化（覆盖索引）
-- 针对 WHERE status=1 AND is_deleted=0 ORDER BY published_at DESC
CREATE INDEX idx_articles_list ON articles(status, is_deleted, published_at DESC, id, title, slug, summary);

-- 2. 热门文章排行优化
CREATE INDEX idx_articles_hot ON articles(status, is_deleted, published_at, view_count, like_count, comment_count);

-- 3. 分类筛选优化
CREATE INDEX idx_articles_category ON articles(category_id, status, is_deleted, published_at DESC);

-- 4. 用户文章列表优化
CREATE INDEX idx_articles_user ON articles(user_id, status, is_deleted, published_at DESC);

-- 5. 评论列表优化
CREATE INDEX idx_comments_article ON comments(article_id, status, is_deleted, parent_id, created_at);

-- 6. 用户关注关系优化
CREATE INDEX idx_follows_following ON user_follows(following_id, follower_id);

-- 7. 查看索引使用情况
SELECT
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    CARDINALITY,
    SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'blog_system'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- 8. 分析慢查询
EXPLAIN SELECT * FROM v_published_articles WHERE category_id = 1 ORDER BY view_count DESC LIMIT 20;