20.2 数据库设计

根据上一节的需求分析，本节将设计博客系统的详细数据库结构。我们将遵循数据库规范化原则，同时兼顾查询性能，为每张表定义字段、数据类型、约束以及必要的索引。

一、数据库整体设计

• 数据库名称：blog_db

• 字符集：utf8mb4，支持全球字符及表情符号

• 排序规则：utf8mb4_unicode_ci，提供准确的Unicode排序与比较

二、表结构详解

1. 用户表 users

存储用户基本信息，用于登录和身份识别。

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名，唯一',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱，唯一',
    password VARCHAR(255) NOT NULL COMMENT '密码哈希',
    nickname VARCHAR(50) COMMENT '昵称，可为空',
    avatar VARCHAR(255) COMMENT '头像URL',
    bio TEXT COMMENT '个人简介',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 
ON UPDATE CURRENT_TIMESTAMP COMMENT '信息更新时间',
    INDEX idx_email (email),                -- 登录查询常用
    INDEX idx_username (username)           -- 登录查询常用
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

说明：

• username 和 email 设为唯一，保证不会重复。

• password 字段长度 255 足以存储各种哈希算法的结果。

• 使用 ON UPDATE CURRENT_TIMESTAMP 自动更新 updated_at。

• 为常用查询字段 email 和 username 建立索引，加速登录验证。

2. 分类表 categories

存储文章分类信息，每个分类有名称和描述。

CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '分类ID',
    name VARCHAR(50) NOT NULL UNIQUE COMMENT '分类名称，唯一',
    description VARCHAR(255) COMMENT '分类描述',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章分类表';

3. 标签表 tags

存储标签信息，标签与文章为多对多关系。

CREATE TABLE tags (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '标签ID',
    name VARCHAR(50) NOT NULL UNIQUE COMMENT '标签名称，唯一',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='标签表';

4. 文章表 posts

存储所有文章，包括标题、内容、状态、作者、分类等。

CREATE TABLE posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '文章ID',
    title VARCHAR(200) NOT NULL COMMENT '文章标题',
    content TEXT NOT NULL COMMENT '文章内容',
    excerpt VARCHAR(500) COMMENT '摘要',
    status ENUM('draft', 'published', 'archived') NOT NULL 
DEFAULT 'draft' COMMENT '文章状态',
    views INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '浏览次数',
    user_id INT UNSIGNED NOT NULL COMMENT '作者ID',
    category_id INT UNSIGNED COMMENT '分类ID，可为NULL（未分类）',
    published_at DATETIME COMMENT '实际发布时间，为NULL表示未发布',
    created_at DATETIME NOT NULL DEFAULT 
CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT 
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    -- 外键约束
    CONSTRAINT fk_posts_user FOREIGN KEY (user_id) 
REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_posts_category FOREIGN KEY (category_id) 
REFERENCES categories(id) ON DELETE SET NULL,
    -- 索引
    INDEX idx_user_id (user_id),
    INDEX idx_category_id (category_id),
    INDEX idx_status_published (status, published_at),   -- 查询已发布文章时常用
    INDEX idx_created_at (created_at),
    FULLTEXT INDEX ft_title_content (title, content)     -- 全文索引，用于搜索
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

说明：

• status 使用枚举限制取值。

• user_id 设为 NOT NULL，每篇文章必须有作者；外键设置为 ON DELETE CASCADE，用户删除时其文章自动删除（可根据业务调整，此处暂定级联删除）。

• category_id 允许为 NULL，表示文章未分类；外键设置为 ON DELETE SET NULL，分类删除后文章保留但分类置空。

• 为 user_id、category_id 建立索引，加速关联查询。

• 组合索引 (status, published_at) 可加速按状态和时间排序的查询。

• 全文索引 ft_title_content 用于对标题和内容进行关键词搜索。

5. 文章-标签关联表 post_tags

实现文章与标签的多对多关系。

CREATE TABLE post_tags (
    post_id INT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    CONSTRAINT fk_post_tags_post FOREIGN KEY (post_id) 
REFERENCES posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_post_tags_tag FOREIGN KEY (tag_id) 
REFERENCES tags(id) ON DELETE CASCADE,
    INDEX idx_tag_id (tag_id)   -- 按标签查询文章时使用
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章-标签关联表';

说明：

• 联合主键 (post_id, tag_id) 确保同一篇文章不会重复添加同一标签。

• 为 tag_id 建立单独索引，以便快速查询某个标签下的文章。

• 外键级联删除：删除文章或标签时，关联记录自动清除。

6. 评论表 comments

存储用户对文章的评论（一级评论，无嵌套）。

CREATE TABLE comments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '评论ID',
    content TEXT NOT NULL COMMENT '评论内容',
    user_id INT UNSIGNED NOT NULL COMMENT '评论用户ID',
    post_id INT UNSIGNED NOT NULL COMMENT '所属文章ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '评论时间',
    updated_at DATETIME NOT NULL DEFAULT 
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    CONSTRAINT fk_comments_user FOREIGN KEY (user_id) 
REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_comments_post FOREIGN KEY (post_id) 
REFERENCES posts(id) ON DELETE CASCADE,
    INDEX idx_post_id (post_id),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评论表';

说明：

• 外键级联删除：用户或文章删除时，相关评论也自动删除。

• 为 post_id 和 user_id 建立索引，加速查询。

三、关系总览图

• users ←→ posts：一对多（通过 user_id）

• categories ←→ posts：一对多（通过 category_id）

• posts ←→ tags：多对多（通过 post_tags 中间表）

• users ←→ comments：一对多（通过 user_id）

• posts ←→ comments：一对多（通过 post_id）

四、设计要点说明

1、字段类型选择

• 整型使用 INT UNSIGNED 扩大取值范围。

• 字符串长度根据实际需求设置，避免过大浪费空间。

• 文本内容使用 TEXT，摘要使用 VARCHAR(500)。

• 时间类型使用 DATETIME，方便跨时区处理（若需要时区感知，可改用 TIMESTAMP）。

2、约束设计

• 主键、唯一约束保证数据的唯一性。

• 外键约束维护数据的参照完整性。

• NOT NULL 与 DEFAULT 结合，保证业务逻辑必须的字段有值。

3、索引策略

• 为所有外键列建立索引，提升连接查询性能。

• 为经常用于过滤和排序的字段（如 status、created_at）建立索引。

• 为全文搜索建立 FULLTEXT 索引，支持高效关键词查询（MySQL 5.6+ InnoDB 支持全文索引）。

• 注意索引并非越多越好，需要在写入性能与查询性能间平衡。

4、存储引擎

• 统一使用 InnoDB，支持事务、外键和行级锁。

5、字符集

• 使用 utf8mb4 避免中文乱码，并支持 emoji 等特殊字符。

五、创建数据库

在创建表之前，先创建数据库：

CREATE DATABASE IF NOT EXISTS blog_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE blog_db;

然后按顺序执行上述 CREATE TABLE 语句（注意由于外键依赖，应先创建 users、categories、tags，再创建 posts，然后 post_tags 和 comments）。

六、数据字典

为了便于查阅，下面以表格形式列出各表的字段概要（略，可根据需要生成）。

七、设计验证

该设计基本满足需求分析中的功能点：

• 支持用户注册、登录。

• 文章有作者、分类、标签、状态、浏览量。

• 标签与文章多对多，通过中间表实现。

• 评论归属于用户和文章。

• 可扩展：如需实现嵌套评论，可在 comments 表中添加 parent_id 字段自关联。