mysql菜鸟教程
20.3 表创建与数据初始化
在上一节中，我们完成了博客系统的数据库逻辑设计。本节将编写具体的 SQL 脚本，创建数据库、数据表，并插入一批初始数据，为后续的查询练习和功能开发做好准备。
一、创建数据库
首先，创建数据库并指定字符集为 utf8mb4，以确保能够完整支持中文和表情符号。
-- 如果数据库已存在，先删除（生产环境慎用！这里仅用于演示重置） DROP DATABASE IF EXISTS blog_db; -- 创建新数据库 CREATE DATABASE blog_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 切换到该数据库 USE blog_db;
二、按顺序创建表
由于表之间存在外键依赖，必须按照依赖顺序创建：
users – 不依赖其他表
categories – 不依赖其他表
tags – 不依赖其他表
posts – 依赖 users 和 categories
post_tags – 依赖 posts 和 tags
comments – 依赖 users 和 posts
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='用户表';
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',
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 '更新时间',
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='文章表';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='文章-标签关联表';
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='评论表';
三、初始化示例数据
为了后续查询练习，我们插入一批有代表性的初始数据。
1. 插入用户
-- 密码使用简单的哈希示例，实际应用中应使用bcrypt等算法加密
INSERT INTO users (username, email, password, nickname, bio) VALUES
('zhangsan', 'zhangsan@example.com', 'hashed_pwd_1', '张三', '热爱技术，喜欢分享'),
('lisi', 'lisi@example.com', 'hashed_pwd_2', '李四', '摄影爱好者，旅行达人'),
('wangwu', 'wangwu@example.com', 'hashed_pwd_3', '王五', '学生，学习编程中');2. 插入分类
INSERT INTO categories (name, description) VALUES
('技术', '编程、开发、技术趋势'),
('生活', '生活随笔、感悟'),
('旅行', '旅行日记、攻略分享');3. 插入标签
INSERT INTO tags (name) VALUES
('MySQL'),
('Python'),
('摄影'),
('旅行'),
('生活');4. 插入文章
INSERT INTO posts (title, content, excerpt, status, views,
user_id, category_id, published_at) VALUES
('MySQL 索引优化实战', '这是一篇关于MySQL索引优化的长文，内容非常详细……',
'索引优化技巧总结', 'published', 120, 1, 1, NOW() - INTERVAL 2 DAY),
('Python 装饰器详解', '装饰器是Python中非常强大的特性……',
'理解Python装饰器', 'published', 85, 1, 1, NOW() - INTERVAL 5 DAY),
('国庆黄山游记', '国庆期间去了黄山，风景美不胜收……', '黄山游记',
'published', 60, 2, 3, NOW() - INTERVAL 10 DAY),
('如何保持学习动力', '学习编程的路上，如何持续保持动力？',
'学习心得', 'draft', 0, 3, 2, NULL),
('数据库设计规范', '良好的数据库设计是系统稳定性的基础……',
'设计规范', 'published', 45, 1, 1, NOW() - INTERVAL 1 DAY);5. 关联文章与标签
-- 文章1（MySQL索引优化）打上 MySQL 标签 INSERT INTO post_tags (post_id, tag_id) VALUES (1, 1); -- 文章2（Python装饰器）打上 Python 标签 INSERT INTO post_tags (post_id, tag_id) VALUES (2, 2); -- 文章3（黄山游记）打上 旅行 和 摄影 标签 INSERT INTO post_tags (post_id, tag_id) VALUES (3, 3), (3, 4); -- 文章5（数据库设计）打上 MySQL 标签 INSERT INTO post_tags (post_id, tag_id) VALUES (5, 1);
6. 插入评论
INSERT INTO comments (content, user_id, post_id) VALUES
('写得太好了，学到了！', 2, 1),
('非常详细，感谢分享', 3, 1),
('照片拍得真美', 1, 3),
('黄山一直想去，看了你的游记更想去了', 3, 3),
('期待你的下一篇', 2, 2);四、验证数据
执行完以上 SQL 后，可以运行几个简单查询验证数据是否正确插入：
-- 查看所有文章及其作者、分类 SELECT p.id, p.title, u.username, c.name AS category, p.views FROM posts p JOIN users u ON p.user_id = u.id LEFT JOIN categories c ON p.category_id = c.id; -- 查看文章1的标签 SELECT p.title, t.name AS tag FROM posts p JOIN post_tags pt ON p.id = pt.post_id JOIN tags t ON pt.tag_id = t.id WHERE p.id = 1; -- 查看文章1的评论 SELECT c.content, u.username, c.created_at FROM comments c JOIN users u ON c.user_id = u.id WHERE c.post_id = 1 ORDER BY c.created_at DESC;
如果都能返回预期的结果，说明数据库创建和数据初始化成功。
五、清理（可选）
如果需要在练习后重置所有数据，可以按以下顺序删除表（注意外键约束，建议先删除从表）：
DROP TABLE IF EXISTS comments; DROP TABLE IF EXISTS post_tags; DROP TABLE IF EXISTS posts; DROP TABLE IF EXISTS tags; DROP TABLE IF EXISTS categories; DROP TABLE IF EXISTS users;
发表评论
所有评论