-- ===== 一对多：一个用户有多篇文章 =====
-- 用户表（"一"的一方）
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL
);
-- 文章表（"多"的一方，存外键）
CREATE TABLE posts (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT,
-- 外键：指向 users 表的 id
-- 类比：前端对象里的 userId 字段，但数据库会强制检查
user_id INT UNSIGNED NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE -- 删除用户时，自动删除其所有文章
ON UPDATE CASCADE -- 更新用户 id 时，自动更新关联
);
🔗 多对多关系（中间表）
-- ===== 多对多：文章和标签 =====
-- 类比：前端的 post.tags = [1, 3, 5]，
-- 但数据库不能在一个字段里存数组，需要"中间表"
-- 标签表
CREATE TABLE tags (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE -- 标签名不能重复
);
-- 中间关联表（核心！）
CREATE TABLE post_tags (
post_id INT UNSIGNED NOT NULL,
tag_id INT UNSIGNED NOT NULL,
PRIMARY KEY (post_id, tag_id), -- 复合主键防重复
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
-- 给文章打标签
INSERT INTO post_tags (post_id, tag_id) VALUES (1, 1);
INSERT INTO post_tags (post_id, tag_id) VALUES (1, 3);
-- 查询某篇文章的所有标签
SELECT t.name
FROM tags t
JOIN post_tags pt ON t.id = pt.tag_id
WHERE pt.post_id = 1;
👤 一对一关系
-- ===== 一对一：用户和详细资料 =====
-- 类比：把一个大的前端 state 对象拆成两个更小的模块
CREATE TABLE user_profiles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL UNIQUE, -- UNIQUE 保证一对一
avatar VARCHAR(500),
phone VARCHAR(20),
address TEXT,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
💡 为什么不用 JSON 数组代替中间表？虽然 MySQL 支持 JSON 类型，也能通过生成列/表达式索引做部分索引优化，但它仍不适合替代关系表：外键约束、标准 JOIN、唯一性约束和关系一致性都会更难维护。数据库的核心优势就是"关系"——用好它。
✏️ 填空：外键语法
CREATE TABLE comments (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
content TEXT NOT NULL,
post_id INT UNSIGNED NOT NULL,
(post_id) posts(id)
ON DELETE CASCADE
);
-- ===== 索引创建语法 =====
-- 普通索引：加速查询，允许重复值
-- 类比：按"城市"给通讯录分组
CREATE INDEX idx_users_email ON users(email);
-- 唯一索引：加速查询 + 保证不重复
-- 类比：Map 的 key 天然不重复
CREATE UNIQUE INDEX uk_users_email ON users(email);
-- 联合索引（复合索引）：多列组合
-- 类比：先按"城市"分，城市内再按"姓名"排
CREATE INDEX idx_posts_user_created
ON posts(user_id, created_at);
-- 建表时直接加索引
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author_id INT UNSIGNED NOT NULL,
status TINYINT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_author (author_id), -- 普通索引
INDEX idx_status_created (status, created_at), -- 联合索引
UNIQUE INDEX uk_title (title) -- 唯一索引
);
📏 最左前缀原则（核心！）
-- ===== 联合索引的最左前缀原则 =====
-- 假设有索引 idx(A, B, C)
-- ✅ 能用到索引（从最左列开始匹配）
SELECT * FROM t WHERE A = 1; -- 用到 A
SELECT * FROM t WHERE A = 1 AND B = 2; -- 用到 A, B
SELECT * FROM t WHERE A = 1 AND B = 2 AND C = 3; -- 用到 A, B, C
-- ❌ 无法用到索引（跳过了最左列）
SELECT * FROM t WHERE B = 2; -- 跳过 A，索引失效
SELECT * FROM t WHERE C = 3; -- 跳过 A 和 B
SELECT * FROM t WHERE B = 2 AND C = 3; -- 跳过 A
-- 类比：电话簿按"城市-区-街道"排序
-- 你可以查"北京市"，也可以查"北京市朝阳区"
-- 但不能直接查"朝阳区"（不知道是哪个城市的朝阳区）
• 不要用 MySQL 保留字做列名 • 不要用 FLOAT 存金额 • 不要在一个字段里存逗号分隔的 ID • 不要给每个字段都建索引 • 不要用 utf8（用 utf8mb4）
🔧 常用 ALTER TABLE 操作
-- ===== 修改表结构（类似前端重构组件 props）=====
-- 添加列
ALTER TABLE users ADD phone VARCHAR(20) AFTER email;
-- 修改列类型
ALTER TABLE users MODIFY bio VARCHAR(1000);
-- 重命名列
ALTER TABLE users CHANGE username nickname VARCHAR(50) NOT NULL;
-- 删除列
ALTER TABLE users DROP COLUMN phone;
-- 添加索引
ALTER TABLE users ADD INDEX idx_status (status);
-- 删除索引
ALTER TABLE users DROP INDEX idx_status;
✏️ 填空：建表语法
CREATE TABLE comments (
id INT UNSIGNED AUTO_INCREMENT ,
content TEXT NOT NULL,
user_id INT UNSIGNED NOT NULL,
post_id INT UNSIGNED NOT NULL,
created_at DEFAULT CURRENT_TIMESTAMP,
INDEX idx_post (post_id),
FOREIGN KEY (user_id) users(id)
) COMMENT='评论表';
🧠 小测验：建表实战
以下哪种做法是错误的？
SQL 查询 · 01
SELECT 基础：数据库版的 Array.filter + Array.map
SELECT 是 SQL 中最常用的语句。前端的 array.filter().map().sort() 链式调用，在 SQL 里变成了 SELECT ... WHERE ... ORDER BY。
🔄 JS 数组方法 vs SQL 对应
JS 方法
SQL 子句
作用
.map(u => u.name)
SELECT name
选择特定字段
.filter(u => u.age > 18)
WHERE age > 18
过滤条件
.sort((a,b) => a.name - b.name)
ORDER BY name
排序
.slice(0, 10)
LIMIT 10
取前 N 条
.slice(10, 20)
LIMIT 10 OFFSET 10
分页
array.length
SELECT COUNT(*)
计数
[...new Set(arr)]
SELECT DISTINCT
去重
📌 基础查询语法
-- ===== SELECT 基础语法 =====
-- 查询所有列（类似 console.log(users)）
SELECT * FROM users;
-- 查询指定列（类似 users.map(u => ({ name: u.name, email: u.email }))）
SELECT username, email FROM users;
-- 带条件过滤（类似 users.filter(u => u.status === 1)）
SELECT * FROM users WHERE status = 1;
-- 多条件（AND / OR）
SELECT * FROM posts
WHERE status = 1 AND user_id = 1;
SELECT * FROM posts
WHERE status = 1 OR view_count > 1000;
-- 模糊搜索（类似 name.includes('ali')）
SELECT * FROM users WHERE username LIKE '%ali%';
-- 范围查询
SELECT * FROM posts WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
SELECT * FROM users WHERE id IN (1, 2, 3);
-- NULL 判断（注意：不能用 = NULL，必须用 IS NULL）
SELECT * FROM users WHERE avatar IS NULL;
SELECT * FROM users WHERE avatar IS NOT NULL;
📊 排序与分页
-- ===== 排序 ORDER BY =====
-- 按创建时间倒序（最新的在前面）
-- 类比：posts.sort((a, b) => b.createdAt - a.createdAt)
SELECT * FROM posts ORDER BY created_at DESC;
-- 多字段排序：先按状态，再按时间
SELECT * FROM posts ORDER BY status ASC, created_at DESC;
-- ===== 分页 LIMIT + OFFSET =====
-- 第一页（每页 10 条）
-- 类比：posts.slice(0, 10)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;
-- 第二页
-- 类比：posts.slice(10, 20)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 10;
-- LIMIT 的简写形式：LIMIT offset, count
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10, 10; -- 等同上面
🏷️ 别名与表达式
-- ===== 别名 AS =====
-- 列别名（类似 map 时重命名字段）
SELECT username AS '用户名', email AS '邮箱' FROM users;
-- 计算列（类似 map 中做计算）
SELECT title, view_count, view_count * 0.1 AS score FROM posts;
-- 字符串拼接
SELECT CONCAT(username, ' <', email, '>') AS display_name FROM users;
-- 条件表达式 CASE（类似三元运算符）
SELECT title,
CASE status
WHEN 0 THEN '草稿'
WHEN 1 THEN '已发布'
ELSE '未知'
END AS status_text
FROM posts;
✏️ 填空：SELECT 练习
-- 查询已发布的文章，按时间倒序，取前 5 条
SELECT title, created_at FROM posts
status = 1
created_at DESC
5;
🧠 小测验：SELECT 基础
判断 NULL 值应该用什么语法？
SQL 查询 · 02
JOIN 详解：跨表查询，数据库最强大的能力
前端从 API 拿到的嵌套 JSON（用户带文章列表），在数据库层其实是通过 JOIN 把多张表"拼"在一起。这是 SQL 区别于 NoSQL 的核心优势。
SELECT * FROM users JOIN posts ON users.id = posts.user_id
两次 fetch，前端拼接
一条 SQL，数据库内部完成
数据量大时浏览器容易卡顿
通常比前端拼接更高效（取决于索引与执行计划）
📌 INNER JOIN（交集）
-- ===== INNER JOIN：只返回两边都匹配的行 =====
-- 类比：两个数组取交集
-- 查询所有文章及其作者信息
SELECT
p.title AS '文章标题',
p.created_at AS '发布时间',
u.username AS '作者'
FROM posts p
INNER JOIN users u ON p.user_id = u.id
WHERE p.status = 1
ORDER BY p.created_at DESC;
-- 如果某篇文章的 user_id 不存在于 users 表，这条记录不会出现
📌 LEFT JOIN（保留左表全部）
-- ===== LEFT JOIN：左表全部保留，右表没匹配到的填 NULL =====
-- 类比：users.map(u => ({ ...u, postCount: posts.filter(p => p.userId === u.id).length }))
-- 即使某用户没有文章，也会出现在结果中
-- 查询所有用户及其文章数（包括没写过文章的用户）
SELECT
u.username AS '用户名',
COUNT(p.id) AS '文章数'
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;
-- 结果可能是：
-- alice | 5
-- bob | 3
-- carol | 0 ← LEFT JOIN 才能看到她（没有文章）
🔗 多表 JOIN
-- ===== 三表 JOIN：文章 + 作者 + 分类 =====
SELECT
p.title AS '文章',
u.username AS '作者',
c.name AS '分类',
p.view_count AS '阅读量'
FROM posts p
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON p.category_id = c.id -- 文章可能没分类
WHERE p.status = 1
ORDER BY p.view_count DESC
LIMIT 10;
-- ===== 多对多 JOIN：文章 + 标签 =====
SELECT
p.title AS '文章',
GROUP_CONCAT(t.name SEPARATOR ', ') AS '标签列表'
FROM posts p
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id
WHERE p.status = 1
GROUP BY p.id, p.title;
💡 INNER JOIN vs LEFT JOIN 选择：如果"右表没数据"代表异常（如文章没作者），用 INNER JOIN。如果"右表没数据"是正常情况（如用户没文章），用 LEFT JOIN。
✏️ 填空：JOIN 语法
-- 查询所有用户和他们的文章（包含没有文章的用户）
SELECT u.username, p.title
FROM users u
JOIN posts p
u.id = p.user_id;
🧠 小测验：JOIN
LEFT JOIN 的特点是什么？
SQL 查询 · 03
聚合函数：数据库版的 Array.reduce
前端用 reduce() 做求和、计数、求平均。SQL 用 GROUP BY + 聚合函数做同样的事——但快几个数量级，因为直接在数据源计算。
🔄 JS reduce vs SQL 聚合
JS 写法
SQL 写法
作用
arr.length
COUNT(*)
计数
arr.reduce((s,x) => s+x.price, 0)
SUM(price)
求和
sum / arr.length
AVG(price)
平均值
Math.max(...arr.map(x => x.price))
MAX(price)
最大值
Math.min(...arr.map(x => x.price))
MIN(price)
最小值
📌 基础聚合
-- ===== 聚合函数基础 =====
-- 文章总数
SELECT COUNT(*) AS '文章总数' FROM posts;
-- 已发布文章数
SELECT COUNT(*) AS '已发布' FROM posts WHERE status = 1;
-- 总阅读量、平均阅读量
SELECT
SUM(view_count) AS '总阅读量',
AVG(view_count) AS '平均阅读量',
MAX(view_count) AS '最高阅读量',
MIN(view_count) AS '最低阅读量'
FROM posts WHERE status = 1;
📊 GROUP BY 分组聚合
-- ===== GROUP BY：按某列分组统计 =====
-- 类比：Object.groupBy(posts, p => p.userId) 然后对每组 reduce
-- 每个用户的文章数
SELECT
u.username AS '作者',
COUNT(p.id) AS '文章数',
SUM(p.view_count) AS '总阅读量'
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY COUNT(p.id) DESC;
-- 每月发布文章数（时间维度统计）
SELECT
DATE_FORMAT(created_at, '%Y-%m') AS '月份',
COUNT(*) AS '发布数'
FROM posts
WHERE status = 1
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY '月份' DESC;
🔍 HAVING：过滤分组结果
-- ===== HAVING：对分组结果做筛选 =====
-- WHERE 过滤的是原始行，HAVING 过滤的是分组后的结果
-- 类比：先 groupBy，再 filter 分组结果
-- 找出发了 5 篇以上文章的活跃用户
SELECT
u.username,
COUNT(p.id) AS post_count
FROM users u
JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
HAVING COUNT(p.id) >= 5 -- 不能用 WHERE，因为 COUNT 是分组后才有的
ORDER BY post_count DESC;
-- SQL 执行顺序（重要！）：
-- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
-- 类比前端：获取数据 → filter → groupBy → filter 分组 → map → sort → slice
⚠️ WHERE vs HAVING：WHERE 在分组前过滤原始行（不能用聚合函数），HAVING 在分组后过滤结果（可以用聚合函数）。记住：WHERE 过滤行，HAVING 过滤组。
✏️ 填空：聚合练习
-- 统计每个分类的文章数，只显示文章数大于 3 的分类
SELECT category_id, AS cnt
FROM posts
category_id
cnt > 3;
-- ===== 标量子查询：返回一个值 =====
-- 类比：const avgViews = posts.reduce(...) / posts.length;
-- const hotPosts = posts.filter(p => p.views > avgViews);
-- 查询阅读量高于平均值的文章
SELECT title, view_count
FROM posts
WHERE view_count > (
SELECT AVG(view_count) FROM posts -- 子查询返回一个数字
)
ORDER BY view_count DESC;
📌 列表子查询（返回一列）
-- ===== 列表子查询：配合 IN 使用 =====
-- 类比：const activeUserIds = users.filter(u => u.active).map(u => u.id);
-- const posts = allPosts.filter(p => activeUserIds.includes(p.userId));
-- 查询活跃用户的所有文章
SELECT title, created_at
FROM posts
WHERE user_id IN (
SELECT id FROM users WHERE status = 1 -- 子查询返回一列
);
-- EXISTS 写法（大数据量时更快）
SELECT p.title
FROM posts p
WHERE EXISTS (
SELECT 1 FROM users u
WHERE u.id = p.user_id AND u.status = 1
);
🏗️ CTE（WITH 子句）——SQL 里的变量声明
-- ===== CTE：Common Table Expression =====
-- 类比：先 const 声明中间变量，再用这个变量
-- const activeUsers = users.filter(u => u.active);
-- const result = activeUsers.map(...);
-- 用 CTE 让复杂查询更易读
WITH active_users AS (
-- 第一步：筛选活跃用户（类似声明一个变量）
SELECT id, username
FROM users
WHERE status = 1
),
user_stats AS (
-- 第二步：统计每个活跃用户的文章数
SELECT
au.username,
COUNT(p.id) AS post_count,
COALESCE(SUM(p.view_count), 0) AS total_views
FROM active_users au
LEFT JOIN posts p ON au.id = p.user_id
GROUP BY au.id, au.username
)
-- 第三步：使用前面的结果
SELECT * FROM user_stats
WHERE post_count > 0
ORDER BY total_views DESC;
💡 CTE vs 子查询选择：CTE 的语义更清晰、可读性更好，推荐在复杂查询中优先使用。性能上两者通常接近；CTE 的优势是可以给中间结果命名并在后续查询中重复引用，而普通子查询往往需要重复书写。
✏️ 填空：子查询练习
-- 用 CTE 查询发文超过 3 篇的用户
prolific AS (
SELECT user_id, COUNT(*) AS cnt
FROM posts GROUP BY user_id
cnt > 3
)
SELECT u.username, p.cnt
FROM users u JOIN prolific p ON u.id = p.user_id;
-- ===== INSERT 各种写法 =====
-- 单条插入
INSERT INTO users (username, email) VALUES ('alice', 'alice@test.com');
-- 批量插入（比循环单条插入快很多！）
-- 类比：一次 fetch 发送数组 vs 循环发 N 次 fetch
INSERT INTO users (username, email) VALUES
('bob', 'bob@test.com'),
('carol', 'carol@test.com'),
('dave', 'dave@test.com');
-- 插入时处理重复（UNIQUE 冲突）
-- 类比：前端 Map.set() 覆盖已有值
INSERT INTO users (username, email) VALUES ('alice', 'alice-new@test.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);
-- 获取刚插入的自增 ID
INSERT INTO posts (title, content, user_id) VALUES ('第一篇', '内容...', 1);
SELECT LAST_INSERT_ID(); -- 返回刚插入的 id
📌 UPDATE 更新数据
-- ===== UPDATE 更新 =====
-- 更新单条记录
UPDATE users SET email = 'newemail@test.com' WHERE id = 1;
-- 更新多个字段
UPDATE posts
SET title = '新标题', status = 1, updated_at = NOW()
WHERE id = 5;
-- 基于计算的更新（类似 setState(prev => prev + 1)）
UPDATE posts SET view_count = view_count + 1 WHERE id = 5;
-- 批量更新（用 CASE WHEN）
UPDATE posts
SET status = CASE
WHEN created_at < '2023-01-01' THEN 0 -- 旧文章设为草稿
ELSE status
END
WHERE user_id = 1;
⚠️ UPDATE/DELETE 不加 WHERE 会作用于全表！这是最危险的操作。永远记得加 WHERE 条件，建议先用 SELECT 验证要操作的数据范围。
📌 DELETE 与软删除
-- ===== DELETE 删除 =====
-- 物理删除（数据彻底消失）
DELETE FROM posts WHERE id = 5;
-- ⚠️ 生产环境推荐：软删除（标记删除，数据还在）
-- 类比：前端标记为 hidden 而不是 DOM.remove()
UPDATE posts SET is_deleted = 1, deleted_at = NOW() WHERE id = 5;
-- 查询时过滤已删除的
SELECT * FROM posts WHERE is_deleted = 0;
-- 批量删除
DELETE FROM posts WHERE status = 0 AND created_at < '2023-01-01';
-- TRUNCATE：清空整张表（比 DELETE 快，不可回滚！）
TRUNCATE TABLE temp_logs;
✏️ 填空：增删改练习
-- 批量插入两条数据
INSERT INTO tags (name)
('JavaScript'), ('MySQL');
-- 将文章阅读量 +1
UPDATE posts SET view_count = WHERE id = 1;
-- ===== 显式加锁（在事务中使用）=====
START TRANSACTION;
-- 共享锁（读锁）：允许其他事务读，不允许改
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- 排他锁（写锁）：不允许其他事务读和改
-- 类比：先读取再更新的场景，防止其他人同时操作
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 拿到锁后安全地修改
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT; -- 提交后自动释放锁
💀 死锁：两个事务互相等待
-- ===== 死锁示例 =====
-- 类比：两个人互相等对方先让路，谁也走不了
-- 事务 A：
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 锁住 id=1
-- ...等待 id=2 的锁
-- 事务 B（同时）：
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- 锁住 id=2
UPDATE accounts SET balance = balance + 50 WHERE id = 1; -- 等待 id=1 的锁 → 死锁！
-- MySQL 检测到死锁后，会自动回滚其中一个事务
-- ERROR 1213: Deadlock found when trying to get lock
-- ===== 避免死锁的方法 =====
-- 1. 按固定顺序加锁（先锁 id 小的，再锁 id 大的）
-- 2. 缩小事务范围，尽快 COMMIT
-- 3. 用合理的索引减少锁范围
// ===== MyBatis Mapper 接口（Java）=====
// 类比：前端的 API service 层
// 1. 定义 Mapper 接口
@Mapper
public interface UserMapper {
// 注解方式：简单查询直接写
@Select("SELECT * FROM users WHERE id = #{id}")
User findById(@Param("id") Integer id);
@Insert("INSERT INTO users (username, email) VALUES (#{username}, #{email})")
@Options(useGeneratedKeys = true, keyProperty = "id")
int insert(User user);
@Update("UPDATE users SET email = #{email} WHERE id = #{id}")
int updateEmail(@Param("id") Integer id, @Param("email") String email);
@Delete("DELETE FROM users WHERE id = #{id}")
int deleteById(@Param("id") Integer id);
}
📝 XML Mapper（复杂查询）
<!-- ===== MyBatis XML Mapper：复杂查询用 XML ===== -->
<mapper namespace="com.example.mapper.PostMapper">
<!-- 动态 SQL：根据条件拼接查询 -->
<!-- 类比：前端的条件渲染 {condition && <Component/>} -->
<select id="searchPosts" resultType="Post">
SELECT * FROM posts
<where>
<if test="status != null">
AND status = #{status}
</if>
<if test="keyword != null">
AND title LIKE CONCAT('%', #{keyword}, '%')
</if>
<if test="userId != null">
AND user_id = #{userId}
</if>
</where>
ORDER BY created_at DESC
LIMIT #{offset}, #{limit}
</select>
</mapper>
// 查询单个用户
@("SELECT * FROM users WHERE id = #{id}")
User findById(@Param("id") Integer id);
// 插入用户
@("INSERT INTO users (name) VALUES (#{name})")
int insert(User user);
-- ===== 博客系统完整建表 =====
-- 用户表
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
avatar VARCHAR(500),
bio VARCHAR(500),
status TINYINT UNSIGNED DEFAULT 1 COMMENT '0=禁用 1=正常',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE INDEX uk_email (email),
UNIQUE INDEX uk_username (username)
) COMMENT='用户表';
-- 文章表
CREATE TABLE posts (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
slug VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
summary VARCHAR(500),
cover_image VARCHAR(500),
user_id INT UNSIGNED NOT NULL,
category_id INT UNSIGNED,
status TINYINT DEFAULT 0 COMMENT '0=草稿 1=已发布 2=已下架',
is_deleted TINYINT(1) DEFAULT 0,
view_count INT UNSIGNED DEFAULT 0,
like_count INT UNSIGNED DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
published_at DATETIME,
UNIQUE INDEX uk_slug (slug),
INDEX idx_user (user_id),
INDEX idx_category (category_id),
INDEX idx_status_published (status, published_at),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) COMMENT='文章表';
-- 评论表（支持楼中楼）
CREATE TABLE comments (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
content TEXT NOT NULL,
user_id INT UNSIGNED NOT NULL,
post_id INT UNSIGNED NOT NULL,
parent_id INT UNSIGNED DEFAULT NULL COMMENT '父评论 ID，NULL=顶级评论',
is_deleted TINYINT(1) DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_post (post_id),
INDEX idx_parent (parent_id),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE SET NULL
) COMMENT='评论表';
🔍 典型业务查询
-- ===== 博客常见查询 =====
-- 1. 首页文章列表（分页、带作者、带分类）
SELECT p.id, p.title, p.summary, p.cover_image,
p.view_count, p.like_count, p.published_at,
u.username AS author, u.avatar AS author_avatar,
c.name AS category
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 1 AND p.is_deleted = 0
ORDER BY p.published_at DESC
LIMIT 10 OFFSET 0;
-- 2. 热门文章排行（7 天内阅读量最高）
SELECT title, view_count, like_count
FROM posts
WHERE status = 1 AND is_deleted = 0
AND published_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY view_count DESC
LIMIT 10;
-- 3. 文章详情 + 评论树
SELECT c.id, c.content, c.parent_id, c.created_at,
u.username, u.avatar
FROM comments c
JOIN users u ON c.user_id = u.id
WHERE c.post_id = 1 AND c.is_deleted = 0
ORDER BY c.created_at ASC;
✏️ 填空：博客查询
-- 查询某用户发布的文章数
SELECT AS post_count
FROM posts
WHERE user_id = 1 AND status = 1 AND = 0;
-- ===== 慢查询日志配置 =====
-- 查看是否已开启
SHOW VARIABLES LIKE 'slow_query_log';
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
-- 设置阈值：超过 1 秒的查询记录下来
SET GLOBAL long_query_time = 1;
-- 查看慢查询日志文件位置
SHOW VARIABLES LIKE 'slow_query_log_file';
-- 查看最近的慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';
🔬 排查流程
-- ===== 慢查询排查四步法 =====
-- 第 1 步：找到慢查询
-- 在慢查询日志或监控工具中发现这条 SQL 耗时 3.2 秒
SELECT * FROM posts WHERE title LIKE '%MySQL%' ORDER BY created_at DESC;
-- 第 2 步：EXPLAIN 分析
EXPLAIN SELECT * FROM posts WHERE title LIKE '%MySQL%' ORDER BY created_at DESC;
-- type=ALL, rows=500000 → 全表扫描 50 万行！
-- 第 3 步：定位原因
-- 原因：LIKE '%xxx%' 无法走索引
-- 且 ORDER BY created_at 导致了文件排序（filesort）
-- 第 4 步：优化方案
-- 方案 A：如果是前缀搜索，改写 LIKE
SELECT * FROM posts WHERE title LIKE 'MySQL%' ORDER BY created_at DESC;
-- 方案 B：为搜索场景建全文索引
ALTER TABLE posts ADD FULLTEXT INDEX ft_title (title);
SELECT * FROM posts WHERE MATCH(title) AGAINST('MySQL' IN BOOLEAN MODE);
-- 方案 C：如果是复杂搜索，考虑 Elasticsearch
-- （超出 MySQL 范围，但要知道边界在哪）
📊 常见优化手段
🟢 索引优化
• 为 WHERE 条件列加索引 • 利用联合索引的最左前缀 • 避免在索引列上使用函数 • 用覆盖索引避免回表