第14章：进阶实战（贴合企业开发，完整项目）

本章通过两个完整的企业级项目，帮助你掌握 MySQL 在实际开发中的应用，从数据库设计到复杂查询，全面提升你的实战能力。

---

实战4：个人博客数据库设计与实现

14.1 需求分析

功能需求：

• 支持用户注册、登录
• 支持文章发布、编辑、删除
• 支持文章分类管理
• 支持评论功能
• 支持文章标签管理
• 支持文章搜索和分页

数据结构：

• 用户表（users）：存储用户信息
• 文章表（articles）：存储文章内容
• 分类表（categories）：存储文章分类
• 评论表（comments）：存储用户评论
• 标签表（tags）：存储文章标签
• 文章标签关联表（article_tags）：存储文章与标签的关联关系

14.2 核心实现

14.2.1 数据库设计

1. 创建表结构

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    avatar VARCHAR(255),
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建分类表
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建文章表
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    user_id INT,
    category_id INT,
    view_count INT DEFAULT 0,
    status ENUM('published', 'draft') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 创建评论表
CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT NOT NULL,
    user_id INT,
    article_id INT,
    parent_id INT NULL,  -- 支持回复功能
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (parent_id) REFERENCES comments(id)
);

-- 创建标签表
CREATE TABLE tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建文章标签关联表
CREATE TABLE article_tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    article_id INT,
    tag_id INT,
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (tag_id) REFERENCES tags(id),
    UNIQUE KEY (article_id, tag_id)  -- 避免重复关联
);

2. 创建索引

-- 为常用查询字段创建索引
CREATE INDEX idx_articles_user_id ON articles(user_id);
CREATE INDEX idx_articles_category_id ON articles(category_id);
CREATE INDEX idx_comments_article_id ON comments(article_id);
CREATE INDEX idx_article_tags_article_id ON article_tags(article_id);
CREATE INDEX idx_article_tags_tag_id ON article_tags(tag_id);

-- 为标题和内容创建全文索引（支持搜索）
CREATE FULLTEXT INDEX idx_articles_title_content ON articles(title, content);

14.2.2 核心查询

1. 文章列表查询

-- 查询文章列表（带分页）
SELECT 
    a.id, a.title, a.content, a.view_count, a.created_at,
    u.username AS author,
    c.name AS category
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 = 'published'
ORDER BY a.created_at DESC
LIMIT 0, 10;

-- 按分类查询文章
SELECT 
    a.id, a.title, a.content, a.view_count, a.created_at,
    u.username AS author
FROM articles a
JOIN users u ON a.user_id = u.id
WHERE a.category_id = 1 AND a.status = 'published'
ORDER BY a.created_at DESC;

2. 文章详情查询

-- 查询文章详情
SELECT 
    a.*,
    u.username AS author,
    c.name AS category
FROM articles a
JOIN users u ON a.user_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
WHERE a.id = 1 AND a.status = 'published';

-- 同时查询文章的标签
SELECT t.name AS tag
FROM article_tags at
JOIN tags t ON at.tag_id = t.id
WHERE at.article_id = 1;

-- 查询文章的评论
SELECT 
    c.id, c.content, c.created_at,
    u.username AS commenter
FROM comments c
JOIN users u ON c.user_id = u.id
WHERE c.article_id = 1 AND c.parent_id IS NULL
ORDER BY c.created_at DESC;

3. 统计查询

-- 统计每个分类的文章数量
SELECT 
    c.name AS category,
    COUNT(a.id) AS article_count
FROM categories c
LEFT JOIN articles a ON c.id = a.category_id AND a.status = 'published'
GROUP BY c.id, c.name
ORDER BY article_count DESC;

-- 统计每个标签的使用次数
SELECT 
    t.name AS tag,
    COUNT(at.article_id) AS usage_count
FROM tags t
LEFT JOIN article_tags at ON t.id = at.tag_id
GROUP BY t.id, t.name
ORDER BY usage_count DESC;

-- 统计用户发布的文章数量
SELECT 
    u.username,
    COUNT(a.id) AS article_count
FROM users u
LEFT JOIN articles a ON u.id = a.user_id AND a.status = 'published'
GROUP BY u.id, u.username
ORDER BY article_count DESC;

4. 搜索功能

-- 全文搜索文章
SELECT 
    a.id, a.title, a.content, a.created_at,
    u.username AS author
FROM articles a
JOIN users u ON a.user_id = u.id
WHERE a.status = 'published' AND MATCH(a.title, a.content) AGAINST('MySQL 教程')
ORDER BY a.created_at DESC;

14.3 实操

完整操作流程：

1. 创建数据库

   CREATE DATABASE blog_system;
   USE blog_system;

2. 创建表结构

   • 执行上面的 CREATE TABLE 语句

3. 创建索引

   • 执行上面的 CREATE INDEX 语句

4. 插入测试数据

   -- 插入用户
   INSERT INTO users (username, password, email) VALUES
   ('admin', '123456', 'admin@example.com'),
   ('user1', '123456', 'user1@example.com');
   
   -- 插入分类
   INSERT INTO categories (name, description) VALUES
   ('技术', '技术相关文章'),
   ('生活', '生活相关文章'),
   ('学习', '学习相关文章');
   
   -- 插入文章
   INSERT INTO articles (title, content, user_id, category_id, status) VALUES
   ('MySQL 基础教程', 'MySQL 是一种开源的关系型数据库...', 1, 1, 'published'),
   ('Python 入门指南', 'Python 是一种简单易学的编程语言...', 1, 1, 'published'),
   ('日常编程技巧', '分享一些实用的编程技巧...', 2, 3, 'published');
   
   -- 插入标签
   INSERT INTO tags (name) VALUES
   ('MySQL'),
   ('Python'),
   ('编程');
   
   -- 关联文章和标签
   INSERT INTO article_tags (article_id, tag_id) VALUES
   (1, 1),
   (1, 3),
   (2, 2),
   (2, 3),
   (3, 3);
   
   -- 插入评论
   INSERT INTO comments (content, user_id, article_id) VALUES
   ('这篇文章很有用，谢谢分享！', 2, 1),
   ('学习了，非常感谢', 1, 2);

5. 测试各种查询

   • 文章列表查询
   • 文章详情查询
   • 分类统计
   • 标签统计
   • 搜索功能

6. 性能优化

   • 分析查询执行计划
   • 优化索引使用

---

实战5：简单后台管理系统数据库设计

14.4 需求分析

功能需求：

• 管理员登录认证
• 用户管理（CRUD操作）
• 数据字典管理
• 系统日志记录
• 权限管理

数据结构：

• 管理员表（admins）：存储管理员信息
• 用户表（users）：存储普通用户信息
• 数据字典表（dictionaries）：存储系统配置数据
• 系统日志表（system_logs）：存储系统操作日志
• 权限表（permissions）：存储权限信息
• 角色表（roles）：存储角色信息
• 角色权限关联表（role_permissions）：存储角色与权限的关联
• 管理员角色关联表（admin_roles）：存储管理员与角色的关联

14.5 核心实现

14.5.1 数据库设计

1. 创建表结构

-- 创建管理员表
CREATE TABLE admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    status ENUM('active', 'inactive') DEFAULT 'active',
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建角色表
CREATE TABLE roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建权限表
CREATE TABLE permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    code VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建角色权限关联表
CREATE TABLE role_permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    role_id INT,
    permission_id INT,
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (permission_id) REFERENCES permissions(id),
    UNIQUE KEY (role_id, permission_id)
);

-- 创建管理员角色关联表
CREATE TABLE admin_roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT,
    role_id INT,
    FOREIGN KEY (admin_id) REFERENCES admins(id),
    FOREIGN KEY (role_id) REFERENCES roles(id),
    UNIQUE KEY (admin_id, role_id)
);

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建数据字典表
CREATE TABLE dictionaries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    type VARCHAR(50) NOT NULL,
    code VARCHAR(50) NOT NULL,
    value VARCHAR(255) NOT NULL,
    description TEXT,
    sort INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY (type, code)
);

-- 创建系统日志表
CREATE TABLE system_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT,
    action VARCHAR(100) NOT NULL,
    target VARCHAR(100) NOT NULL,
    ip VARCHAR(50),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id)
);

2. 创建索引

-- 为常用查询字段创建索引
CREATE INDEX idx_admins_username ON admins(username);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_system_logs_admin_id ON system_logs(admin_id);
CREATE INDEX idx_system_logs_created_at ON system_logs(created_at);
CREATE INDEX idx_dictionaries_type ON dictionaries(type);

14.5.2 核心查询

1. 管理员登录

-- 验证管理员登录
SELECT a.*, r.name AS role_name
FROM admins a
LEFT JOIN admin_roles ar ON a.id = ar.admin_id
LEFT JOIN roles r ON ar.role_id = r.id
WHERE a.username = 'admin' AND a.password = '123456' AND a.status = 'active';

-- 更新最后登录时间
UPDATE admins 
SET last_login_at = CURRENT_TIMESTAMP 
WHERE id = 1;

-- 记录登录日志
INSERT INTO system_logs (admin_id, action, target, ip, user_agent) VALUES
(1, 'login', 'admin', '127.0.0.1', 'Mozilla/5.0...');

2. 用户管理

-- 查询用户列表（带分页）
SELECT * FROM users 
ORDER BY created_at DESC 
LIMIT 0, 20;

-- 按状态筛选用户
SELECT * FROM users 
WHERE status = 'active' 
ORDER BY created_at DESC;

-- 搜索用户
SELECT * FROM users 
WHERE name LIKE '%张%' OR username LIKE '%张%' 
ORDER BY created_at DESC;

-- 统计用户状态分布
SELECT 
    status,
    COUNT(*) AS count
FROM users 
GROUP BY status;

3. 权限管理

-- 查询角色的权限
SELECT p.*
FROM permissions p
JOIN role_permissions rp ON p.id = rp.permission_id
WHERE rp.role_id = 1;

-- 查询管理员的权限
SELECT DISTINCT p.*
FROM permissions p
JOIN role_permissions rp ON p.id = rp.permission_id
JOIN admin_roles ar ON rp.role_id = ar.role_id
WHERE ar.admin_id = 1;

4. 数据字典管理

-- 查询指定类型的数据字典
SELECT * FROM dictionaries 
WHERE type = 'user_status' 
ORDER BY sort ASC;

-- 查询所有数据字典类型
SELECT DISTINCT type FROM dictionaries;

5. 系统日志查询

-- 查询系统日志（带分页）
SELECT 
    sl.*,
    a.username AS admin_name
FROM system_logs sl
LEFT JOIN admins a ON sl.admin_id = a.id
ORDER BY sl.created_at DESC
LIMIT 0, 50;

-- 按操作类型统计日志
SELECT 
    action,
    COUNT(*) AS count
FROM system_logs 
GROUP BY action
ORDER BY count DESC;

14.6 实操

完整操作流程：

1. 创建数据库

   CREATE DATABASE admin_system;
   USE admin_system;

2. 创建表结构

   • 执行上面的 CREATE TABLE 语句

3. 创建索引

   • 执行上面的 CREATE INDEX 语句

4. 初始化数据

   -- 插入管理员
   INSERT INTO admins (username, password, name, email) VALUES
   ('admin', '123456', '管理员', 'admin@example.com');
   
   -- 插入角色
   INSERT INTO roles (name, description) VALUES
   ('超级管理员', '拥有所有权限'),
   ('普通管理员', '拥有部分权限');
   
   -- 插入权限
   INSERT INTO permissions (name, code, description) VALUES
   ('用户管理', 'user_manage', '管理用户信息'),
   ('数据字典管理', 'dict_manage', '管理数据字典'),
   ('系统日志查看', 'log_view', '查看系统日志');
   
   -- 关联角色和权限
   INSERT INTO role_permissions (role_id, permission_id) VALUES
   (1, 1), (1, 2), (1, 3),  -- 超级管理员拥有所有权限
   (2, 1), (2, 3);           -- 普通管理员拥有部分权限
   
   -- 关联管理员和角色
   INSERT INTO admin_roles (admin_id, role_id) VALUES
   (1, 1);  -- 第一个管理员是超级管理员
   
   -- 插入数据字典
   INSERT INTO dictionaries (type, code, value, description, sort) VALUES
   ('user_status', 'active', '活跃', '用户状态-活跃', 1),
   ('user_status', 'inactive', '禁用', '用户状态-禁用', 2),
   ('admin_status', 'active', '活跃', '管理员状态-活跃', 1),
   ('admin_status', 'inactive', '禁用', '管理员状态-禁用', 2);
   
   -- 插入测试用户
   INSERT INTO users (username, password, name, email, phone) VALUES
   ('user1', '123456', '用户1', 'user1@example.com', '13800138001'),
   ('user2', '123456', '用户2', 'user2@example.com', '13800138002'),
   ('user3', '123456', '用户3', 'user3@example.com', '13800138003');

5. 测试各种功能

   • 管理员登录
   • 用户管理操作
   • 权限验证
   • 数据字典查询
   • 系统日志查看

6. 性能优化

   • 分析查询执行计划
   • 优化索引使用
   • 测试大数据量下的性能

---

14.7 实战总结

实战项目	核心知识点	技能收获
个人博客系统	多表设计、全文搜索、复杂查询	掌握内容管理系统数据库设计
后台管理系统	权限管理、日志记录、数据字典	掌握企业级系统数据库设计

企业级开发技巧：

• 数据库设计：合理规划表结构，使用外键保证数据完整性
• 索引优化：为常用查询字段创建索引，提高查询效率
• 安全性：使用事务保证数据一致性，记录系统操作日志
• 可扩展性：设计灵活的数据结构，支持未来功能扩展
• 性能考虑：优化查询语句，避免全表扫描

项目部署建议：

• 使用 MySQL 8.0 或更高版本
• 配置合理的数据库参数
• 定期备份数据库
• 监控数据库性能

下一步： 进入第五部分，学习 MySQL 安全优化与避坑提升！