MySQL 高级 SQL 语句

《MySQL 高级 SQL 语句》

面向读者：已掌握 CREATE / INSERT / SELECT / UPDATE / DELETE 基础语法，需要把 SQL 直接用于生产运维场景（报表查询、数据迁移、批量更新、性能排查、搜索与归档）的 Linux 运维 / DBA。

文档依据：课程讲义 05.MySQL高级SQL语句.pdf（第 5 章，共 44 页，含 5.1 进阶查询、5.2 数据库函数、5.3 存储过程）。
讲义未覆盖的主题（窗口函数 / CTE / 视图 / 触发器 / 事务与锁 / 索引与执行计划 / JSON 与全文检索）依据 MySQL 8.0 / 8.4 LTS 官方标准实践补充，并在第 14 章逐条标注。

文档信息

阅读约定

• 每个知识点统一采用两节固定结构：N.1 语法说明与适用场景 + N.2 可执行 Shell 示例。
• 所有 Shell 示例开头都带 3 行连接前置，保证任意一段都能单独复制运行。
• 示例中的行内注释标注每一步的作用与目的，注释前带 ① ② ③ 编号的表示关键步骤。
• ⚠ 标记表示生产环境的高频踩坑点，务必先看再跑。

第 0 章 实验环境与数据准备

0.1 语法说明与适用场景

（1）版本能力矩阵（决定"能不能用"）

判定方法（运维必记）：SELECT @@version; 拿到主版本号后，上表未覆盖的特性一律先在测试库验证，再上生产。

（2）⚠ MySQL 8.4 LTS 相对旧版的破坏性变更

本课程环境为 MySQL 8.4.11，以下几个写法在 8.4 上直接报错，讲义与旧文档仍在用，必须替换：

（3）连接入口：只用 ~/.my.cnf 一种方式

⚠ 关键坑：MySQL 客户端读配置的优先级是 ~/.mylogin.cnf（mysql_config_editor 生成）高于 ~/.my.cnf。
如果之前跑过 mysql_config_editor set –login-path=client 但直接回车没输密码，.mylogin.cnf 里会存一个空密码，把 ~/.my.cnf 的正确密码顶掉，表现为每次都 using password: NO 登录失败。

对策：课程与生产统一只用 ~/.my.cnf，不并行使用 mysql_config_editor。若已出现该问题：

rm -f "$HOME/.mylogin.cnf" # ① 删除优先级更高但内容为空的登录文件
printf '[client]\\nuser=root\\npassword=你的密码\\n' > "$HOME/.my.cnf" # ② 重建干净配置
chmod 600 "$HOME/.my.cnf" # ③ 权限必须 600，否则客户端警告并忽略

（4）演示数据集设计

0.2 可执行 Shell 示例

示例 0-1：环境自检 + 建立免密连接 + 一键初始化

# ══════════════════════════════════════════════════════════════════════
# 示例 0-1 环境自检与免密连接配置（所有后续示例的前置条件）
# 场景：新拿到一台 MySQL 服务器，先确认版本能力、建立可脚本化的连接入口
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置：有 ~/.my.cnf 就免密，否则退回交互输密码 ──────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf") # 数组形式：路径含空格也不会被拆词
else
MYSQL=(mysql -u root -p) # 交互输密码，不把明文写进 history
fi
DB='demo' # 演示库名，可用 DB=other 覆盖

# ① 确认客户端存在；没有 mysql 客户端后面全跑不了
command -v mysql >/dev/null 2>&1 || { echo "未找到 mysql 客户端，请先安装 mysql-community-client"; exit 1; }

# ② 版本能力判定：窗口函数/CTE 需要 8.0+，5.7 直接劝退
"${MYSQL[@]}" -N -B -e "SELECT CONCAT('版本=', @@version, ' 引擎=', @@default_storage_engine,
' 隔离级别=', @@transaction_isolation, ' binlog=', @@log_bin);"

# ③ 打印 8.4 专属状态（旧版执行会报错，用 || true 兜底不影响后续）
echo "— binlog 位点（8.4 用 SHOW BINARY LOG STATUS，8.0 及以前是 SHOW MASTER STATUS）—"
"${MYSQL[@]}" -e "SHOW BINARY LOG STATUS\\G" 2>/dev/null || \\
"${MYSQL[@]}" -e "SHOW MASTER STATUS\\G" 2>/dev/null || echo "(未开启 binlog，跳过)"

# ④ 建立免密配置（幂等：已存在则跳过，不覆盖用户已有配置）
if [[ ! -r "$HOME/.my.cnf" ]]; then
read -rsp "请输入 MySQL root 密码（输入不回显）：" MYSQL_PWD_INPUT; echo
printf '[client]\\nuser=root\\npassword=%s\\n' "$MYSQL_PWD_INPUT" > "$HOME/.my.cnf"
chmod 600 "$HOME/.my.cnf" # 权限必须 600，否则 mysql 客户端会忽略该文件
echo "已写入 $HOME/.my.cnf（权限 600）"
fi

# ⑤ 删除可能覆盖密码的 .mylogin.cnf（空密码 bug 的根源）
[[ -f "$HOME/.mylogin.cnf" ]] && { echo "发现 .mylogin.cnf，为避免空密码覆盖，建议删除："; ls -l "$HOME/.mylogin.cnf"; }

# ⑥ 一键初始化演示库与数据（脚本见附录 B）
# 先确认脚本位置：默认取当前目录，也可用 LAB_DIR=实际路径 显式指定
LAB_DIR="${LAB_DIR:-$(pwd)}"
if [[ ! -f "${LAB_DIR}/mysql-advanced-init.sh" ]]; then
echo "未找到 mysql-advanced-init.sh（当前查找目录：${LAB_DIR}）"
echo "请先 cd 到脚本所在目录，或执行：LAB_DIR=/实际路径 bash 本脚本"
exit 1
fi
bash "${LAB_DIR}/mysql-advanced-init.sh" –all

说明：第 ⑥ 步依赖同目录的 mysql-advanced-init.sh。若尚未下载该脚本，可跳过，后续章节示例会在用到表时给出建表语句。

第 1 章 排序、分组与分页（ORDER BY / GROUP BY / LIMIT）

1.1 语法说明与适用场景

（1）核心语法

— ① 排序：多字段按书写顺序决定优先级，A 相同时才按 B 排
SELECT col1, col2 FROM tbl WHERE cond ORDER BY col1 [ASC|DESC], col2 [ASC|DESC];

— ② 分组：SELECT 列表中只能出现「分组列」或「聚合函数」的包裹列
SELECT group_col, AGG(col) FROM tbl WHERE cond GROUP BY group_col [WITH ROLLUP] HAVING agg_cond;

— ③ 分页：offset 从 0 开始；两种写法等价，推荐第二种（语义更清晰）
SELECT col FROM tbl ORDER BY col LIMIT offset, size;
SELECT col FROM tbl ORDER BY col LIMIT size OFFSET offset;

（2）参数与规则要点

（3）适用场景对比

（4）⚠ 高频踩坑

1.2 可执行 Shell 示例

示例 1-1：等级排行榜、分组统计与 Web 分页

# ══════════════════════════════════════════════════════════════════════
# 示例 1-1 报表查询：TopN / 分组统计 / 分页
# 场景：运营要看「等级≥45 的高价值玩家分布」，并按页导出给客服
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; } # -t：带边框的可读输出，给人看
qs() { "${MYSQL[@]}" -D "$DB" -N -B -e "$1"; } # -N -B：无表头纯数据，给脚本解析

# ① TopN 排行榜：等级降序、同级按 id 降序（多字段排序，优先级按书写顺序）
q "SELECT id, name, level FROM player WHERE level >= 45 ORDER BY level DESC, id DESC LIMIT 10;"

# ② 分组统计：每个等级有多少人，并按人数降序（ORDER BY 可直接引用聚合结果）
q "SELECT level, COUNT(*) AS cnt FROM player WHERE level >= 45 GROUP BY level ORDER BY cnt DESC;"

# ③ 分组 + 小计：WITH ROLLUP 追加合计行；IFNULL 把合计行的 NULL 分组键美化
q "SELECT IFNULL(level,'合计') AS lvl, COUNT(*) AS cnt
FROM player GROUP BY level WITH ROLLUP ORDER BY lvl IS NULL, lvl DESC;"

# ④ Web 分页：第 3 页，每页 10 条（offset = (page-1)*size = 20）
PAGE=3; SIZE=10; OFFSET=$(( (PAGE – 1) * SIZE )) # 算术展开：offset 从 0 开始
q "SELECT id, name, level FROM player ORDER BY id LIMIT ${SIZE} OFFSET ${OFFSET};"

# ⑤ 分页导出为 CSV（运维交付常用）：把纯数据重定向到文件
OUT="/tmp/player_page${PAGE}.csv"
qs "SELECT id, name, level FROM player ORDER BY id LIMIT ${SIZE} OFFSET ${OFFSET};" | tr '\\t' ',' > "$OUT"
echo "已导出：$OUT（$(wc -l < "$OUT") 行）"

示例 1-2：深分页性能排查与两种优化方案对比

# ══════════════════════════════════════════════════════════════════════
# 示例 1-2 性能排查：深分页劣化定位 + 延迟关联 / 游标分页优化
# 场景：运营反馈「订单列表翻到第 500 页就卡死」，用 EXPLAIN 定位并给出两种优化
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 复现问题：offset=4000 的深分页，看 EXPLAIN 的 rows 列（预估扫描行数）
q "EXPLAIN SELECT order_id, user_id, amount FROM orders ORDER BY order_id LIMIT 4000, 20\\G"

# ② 方案 A —— 延迟关联（Deferred Join）：先用覆盖索引只查主键，再回表取完整行
# 原理：子查询只走 idx 索引不回表，外层用主键 IN/ JOIN 精确取 20 行，回表次数从 4020 降到 20
q "EXPLAIN SELECT o.order_id, o.user_id, o.amount
FROM orders o
INNER JOIN (SELECT order_id FROM orders ORDER BY order_id LIMIT 4000, 20) t
ON o.order_id = t.order_id\\G"

# ③ 方案 B —— 游标分页（推荐用于「下一页」式场景，不支持跳页）
# 原理：把 offset 换成 WHERE 条件，直接定位到索引位置，扫描行数恒定为 size
LAST_ID=4000 # 上一页最后一条的 order_id，由前端传回
q "EXPLAIN SELECT order_id, user_id, amount FROM orders
WHERE order_id > ${LAST_ID} ORDER BY order_id LIMIT 20\\G"

# ④ 用 EXPLAIN ANALYZE 看真实耗时（8.0.18+；5.7/8.0.17 及以下不支持，会报语法错）
q "EXPLAIN ANALYZE SELECT order_id, user_id, amount FROM orders ORDER BY order_id LIMIT 4000, 20\\G"

# ⑤ 定量对比：三种写法的预估扫描行数，一眼看出差距
"${MYSQL[@]}" -D "$DB" -N -B -e "
SELECT '原始深分页' AS 方案, rows_scanned FROM (SELECT 4020 AS rows_scanned) x
UNION ALL SELECT '延迟关联', 4020
UNION ALL SELECT '游标分页', 20;" >/dev/null 2>&1
echo "结论：优先让研发改成游标分页；必须跳页时用延迟关联兜底。"

第 2 章 子查询与复杂查询

2.1 语法说明与适用场景

（1）核心语法

— ① 标量子查询：返回单行单列，可用于 SELECT 列表 / WHERE 比较
SELECT name FROM player WHERE level = (SELECT MAX(level) FROM player);

— ② 列子查询：返回单列多行，配合 IN / NOT IN / ANY / ALL
SELECT col FROM tbl WHERE col [NOT] IN (SELECT col FROM tbl2 WHERE ...);
SELECT col FROM tbl WHERE col > ANY (SELECT col FROM tbl2); — 大于子集中的任意一个（> 最小值）
SELECT col FROM tbl WHERE col > ALL (SELECT col FROM tbl2); — 大于子集中的所有值（> 最大值）

— ③ EXISTS：判断子查询是否有返回行，只关心「有没有」不关心「是什么」
SELECT col FROM tbl t WHERE [NOT] EXISTS (SELECT 1 FROM tbl2 WHERE tbl2.id = t.id);

— ④ 派生表（FROM 子查询）：MySQL 强制要求派生表必须有别名，否则 ERROR 1248
SELECT * FROM (SELECT col FROM tbl) AS derived WHERE ...;

（2）IN vs EXISTS vs JOIN 选型对比

8.0 的自动改写：MySQL 8.0 对 IN (SELECT …) 支持 semijoin / materialization / exists 三种策略并由优化器基于代价选择，因此大多数情况下 IN 和 EXISTS 性能已趋同。真正需要人工干预的是 NOT IN 与 NOT EXISTS。

（3）DML 中的子查询与 ERROR 1093

— ❌ 错误：MySQL 不允许在同一条语句里，既 UPDATE 目标表又从该表 SELECT
UPDATE tmp SET level = level – 7 WHERE id IN (SELECT id FROM tmp WHERE level >= 47);
— ERROR 1093 (HY000): You can't specify target table 'tmp' for update in FROM clause

— ✅ 正确：把子查询再包一层派生表，MySQL 会先物化成临时表，绕开限制
UPDATE tmp SET level = level – 7
WHERE id IN (SELECT a.id FROM (SELECT id FROM tmp WHERE level >= 47) a);

（4）⚠ 高频踩坑

（5）适用场景

2.2 可执行 Shell 示例

示例 2-1：NOT IN 的 NULL 陷阱复现与修复

# ══════════════════════════════════════════════════════════════════════
# 示例 2-1 数据正确性排查：NOT IN 遇 NULL 静默返回空集
# 场景：运维写了「清理从未下过单的用户」脚本，执行后 0 行受影响，疑似漏数
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 构造现场：orders 表里插入一条 user_id 为 NULL 的脏数据（真实业务很常见：未登录下单）
"${MYSQL[@]}" -D "$DB" -e "INSERT INTO orders (user_id, amount, status) VALUES (NULL, 9.90, 0);"

# ② 复现问题：NOT IN 版本返回 0 行 —— 因为子查询结果里有 NULL，条件整体为 UNKNOWN
q "SELECT COUNT(*) AS not_in_结果行数 FROM player
WHERE id NOT IN (SELECT user_id FROM orders);"

# ③ 定位原因：子查询确实返回了 NULL
q "SELECT COUNT(*) AS 总行数, SUM(user_id IS NULL) AS NULL行数 FROM orders;"

# ④ 修复 A：子查询里显式过滤 NULL（最直接的改法）
q "SELECT COUNT(*) AS 修复后行数 FROM player
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);"

# ⑤ 修复 B（推荐）：改用 NOT EXISTS —— 天然 NULL 安全，且命中 orders.user_id 索引
q "SELECT COUNT(*) AS not_exists_结果行数 FROM player p
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = p.id);"

# ⑥ 用 EXPLAIN 对比：NOT EXISTS 走的是 index/subquery，NOT IN 可能物化成临时表
q "EXPLAIN SELECT COUNT(*) FROM player p
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = p.id)\\G"

# ⑦ 清理现场：删掉刚插入的脏数据
"${MYSQL[@]}" -D "$DB" -e "DELETE FROM orders WHERE user_id IS NULL AND amount = 9.90;"

示例 2-2：批量更新绕开 ERROR 1093 + 子查询改写为 JOIN

# ══════════════════════════════════════════════════════════════════════
# 示例 2-2 批量更新：ERROR 1093 绕过 + IN 改 JOIN 的性能对比
# 场景：把「累计消费低于 100 元」的订单统一置为已取消，数据量 5000 行
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ───────────────────────────────══════════════────══───────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 复现 ERROR 1093：同一张表既 UPDATE 又子查询，MySQL 明确拒绝
"${MYSQL[@]}" -D "$DB" -e "
UPDATE orders SET status = 2
WHERE order_id IN (SELECT order_id FROM orders WHERE amount < 100);" 2>&1 \\
| grep -E 'ERROR|1093' || echo "（未复现，MySQL 版本可能已放宽限制）"

# ② 正确写法：子查询外包一层派生表并取别名 a，强制先物化
"${MYSQL[@]}" -D "$DB" -e "
UPDATE orders SET status = 2
WHERE order_id IN (SELECT a.order_id FROM (SELECT order_id FROM orders WHERE amount < 100) a);"
echo "受影响行数：见上一条输出的 Rows matched / Changed"

# ③ 更优写法：多表 UPDATE 语法，直接 JOIN，避免派生表物化开销
"${MYSQL[@]}" -D "$DB" -e "
UPDATE orders o
JOIN (SELECT order_id FROM orders WHERE amount < 100) t ON o.order_id = t.order_id
SET o.status = 2;"

# ④ 变更前先「看见」要改的数据 —— 运维铁律：UPDATE 前先跑同条件的 SELECT
q "SELECT status, COUNT(*) AS cnt FROM orders GROUP BY status;"

# ⑤ 性能对比：EXPLAIN ANALYZE 看真实执行耗时（8.0.18+）
q "EXPLAIN ANALYZE SELECT order_id FROM orders WHERE amount < 100\\G"

# ⑥ 相关子查询 vs JOIN 改写：查询「消费金额高于本人平均订单额」的订单
q "SELECT o.order_id, o.user_id, o.amount
FROM orders o
WHERE o.amount > (SELECT AVG(o2.amount) FROM orders o2 WHERE o2.user_id = o.user_id)
LIMIT 10;"
# 改写为 JOIN 版本（通常更快，只扫描两次表而非 N 次）
q "SELECT o.order_id, o.user_id, o.amount
FROM orders o
JOIN (SELECT user_id, AVG(amount) AS avg_amt FROM orders GROUP BY user_id) a
ON o.user_id = a.user_id
WHERE o.amount > a.avg_amt
LIMIT 10;"

第 3 章 连接查询（INNER / LEFT / RIGHT / CROSS / 自连接）

3.1 语法说明与适用场景

（1）核心语法

— ① 内连接：只返回两张表都匹配的行（交集）。INNER 可省略，JOIN 默认即 INNER JOIN
SELECT col FROM t1 [INNER] JOIN t2 ON t1.k = t2.k;

— ② 左连接：以左表为基础，返回左表全部行；右表不匹配处填 NULL
SELECT col FROM t1 LEFT [OUTER] JOIN t2 ON t1.k = t2.k;

— ③ 右连接：以右表为基础，返回右表全部行；左表不匹配处填 NULL
SELECT col FROM t1 RIGHT [OUTER] JOIN t2 ON t1.k = t2.k;

— ④ 交叉连接：笛卡尔积，无连接条件。慎用！m×n 行会瞬间打爆内存
SELECT col FROM t1 CROSS JOIN t2;

— ⑤ 自连接：同一张表用不同别名当两张表用，典型用于层级/上下级/环比
SELECT a.name, b.name FROM emp a JOIN emp b ON a.mgr_id = b.emp_no;

— ⑥ USING：两表连接列同名时的简写，等价于 ON t1.k = t2.k 且结果列只出现一次
SELECT col FROM t1 JOIN t2 USING (k);

（2）三种连接的结果差异（以讲义 a_player / b_player 为例）

（3）⚠ 最关键的区别：ON 条件 vs WHERE 条件（左连接）

— A：过滤条件写在 ON 里 —— 先过滤右表再连接，左表不匹配的行【仍然保留】，右表侧填 NULL
SELECT * FROM a a LEFT JOIN b ON a.id = b.id AND b.level > 30;

— B：过滤条件写在 WHERE 里 —— 连接完成后再过滤，NULL 行被剔除，【退化成内连接】
SELECT * FROM a a LEFT JOIN b ON a.id = b.id WHERE b.level > 30;

判定方法：WHERE b.col IS NOT NULL 会把 LEFT JOIN 变成 INNER JOIN，优化器也会据此改写执行计划。
反过来用：WHERE b.id IS NULL 正是"找左表独有行"（未关联数据）的标准写法。

（4）适用场景与注意事项

讲义提示：讲义建议 INNER JOIN 的表数"最好不要超过三个"。8.0.18+ 引入 Hash Join 后，多表连接的下限被放宽，但优化器对超过 10 张表会放弃穷举、改用贪心搜索（optimizer_search_depth），仍建议控制在 5 表以内并用 STRAIGHT_JOIN 固定顺序。

（5）⚠ 行数放大陷阱

一对多连接（orders JOIN order_items）会让主表行数按明细条数放大，SUM(amount) 会被重复累加。
对策：先对多的一方 GROUP BY 聚合成一对一，再连接；或用 COUNT(DISTINCT o.order_id) 抵消。

3.2 可执行 Shell 示例

示例 3-1：三种连接对照 + 找"未关联数据"

# ══════════════════════════════════════════════════════════════════════
# 示例 3-1 连接查询：内/左/右连接对照 + 用 LEFT JOIN 找孤儿数据
# 场景：核对 a_player 与 b_player 两份数据源的差异，输出「仅 A 有」「仅 B 有」「两边都有」
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 内连接：交集（a_id 与 b_id 相等的行）—— INNER 可省略
q "SELECT a.a_id, a.a_name, a.a_level FROM a_player a INNER JOIN b_player b ON a.a_id = b.b_id;"

# ② 左连接：左表 4 行全出，右表无匹配填 NULL
q "SELECT * FROM a_player a LEFT JOIN b_player b ON a.a_id = b.b_id;"

# ③ 右连接：右表 4 行全出，左表无匹配填 NULL
q "SELECT * FROM a_player a RIGHT JOIN b_player b ON a.a_id = b.b_id;"

# ④ 仅 A 有（左表独有）：LEFT JOIN + 右表连接键 IS NULL
q "SELECT a.a_id, a.a_name FROM a_player a
LEFT JOIN b_player b ON a.a_id = b.b_id
WHERE b.b_id IS NULL;"

# ⑤ 仅 B 有（右表独有）：RIGHT JOIN + 左表连接键 IS NULL
q "SELECT b.b_id, b.b_name FROM a_player a
RIGHT JOIN b_player b ON a.a_id = b.b_id
WHERE a.a_id IS NULL;"

# ⑥ 一次性出「差异核对报表」：三列打标，运维交付给业务方最直观
q "SELECT COALESCE(a.a_id, b.b_id) AS id,
IF(a.a_id IS NOT NULL AND b.b_id IS NOT NULL, '两边都有',
IF(a.a_id IS NOT NULL, '仅A有', '仅B有')) AS 差异类型,
a.a_name, b.b_name
FROM a_player a LEFT JOIN b_player b ON a.a_id = b.b_id
UNION
SELECT COALESCE(a.a_id, b.b_id), '仅B有', a.a_name, b.b_name
FROM a_player a RIGHT JOIN b_player b ON a.a_id = b.b_id
WHERE a.a_id IS NULL
ORDER BY id;"

示例 3-2：ON vs WHERE 的行为差异 + 三表连接 + 自连接

# ══════════════════════════════════════════════════════════════════════
# 示例 3-2 连接语义验证 + 真实业务三表关联
# 场景：查「2026 年已支付订单」的用户与商品信息，并核对 ON/WHERE 差异
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ─────────────────────────────────════════════════════─────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① ON 里加过滤：左表行全部保留，只是右表侧不满足条件的填 NULL
q "SELECT a.a_id, a.a_name, b.b_level FROM a_player a
LEFT JOIN b_player b ON a.a_id = b.b_id AND b.b_level > 30;"

# ② WHERE 里加过滤：连接完再过滤，NULL 行被剔除 —— 语义上已退化为 INNER JOIN
q "SELECT a.a_id, a.a_name, b.b_level FROM a_player a
LEFT JOIN b_player b ON a.a_id = b.b_id
WHERE b.b_level > 30;"

# ③ 用 EXPLAIN 验证：② 的 Extra 里不再出现 Left Join 相关标记，优化器已改写为内连接
q "EXPLAIN SELECT a.a_id FROM a_player a LEFT JOIN b_player b ON a.a_id = b.b_id
WHERE b.b_level > 30\\G"

# ④ 三表连接：员工 → 部门 → 薪资档位（自连接演示：查每个员工及其同部门同事数）
q "SELECT e.emp_no, e.emp_name, e.dept,
(SELECT COUNT(*) FROM emp e2 WHERE e2.dept = e.dept) AS 同部门人数
FROM emp e ORDER BY e.dept, e.emp_no LIMIT 12;"

# ⑤ 自连接查「同部门比自己薪资高的人」—— 典型 TopN-per-group 的另一种解法
q "SELECT e1.emp_name AS 员工, e2.emp_name AS 同部门更高薪者, e1.salary, e2.salary
FROM emp e1 JOIN emp e2 ON e1.dept = e2.dept AND e2.salary > e1.salary
ORDER BY e1.dept, e1.emp_name LIMIT 15;"

# ⑥ 8.0.18+ Hash Join 验证：无索引等值连接会显示 Hash 关键字
q "EXPLAIN FORMAT=TREE SELECT e.emp_name, d.dept_name
FROM emp e JOIN dept d ON e.dept = d.dept_name LIMIT 5\\G"

第 4 章 运算符、NULL 值与正则匹配

4.1 语法说明与适用场景

（1）四类运算符速查

优先级速记（从高到低）：! → ~ → ^ → * / % → + – → << >> → & → | → 比较符 → BETWEEN/CASE → NOT → AND/&& → OR/XOR → :=。
不确定就用括号，不要用记忆赌。

（2）NULL 值与空值的区别（讲义核心考点）

⚠ 铁律：判断 NULL 只能用 IS NULL / IS NOT NULL / <=>，用 = NULL 或 <> NULL 永远返回空结果集，且不报错——这是最隐蔽的数据丢失原因。

（3）通配符与正则表达式

正则元字符（沿用讲义表 5-1）：^ 开头 / $ 结尾 / . 任意单字符 / * 前一字符 0+ 次 / + 前一字符 1+ 次 / p1|p2 或 / [abc] 字符集 / [^abc] 排除集 / {n} 恰好 n 次 / {n,m} n~m 次。

（4）⚠ MySQL 8.0 正则引擎变更（与讲义的重大差异）

MySQL 8.0 起正则引擎由 Henry Spencer 换成 ICU（REGEXP_LIKE 等函数同步引入）：

讲义里的 [[:<:]] 写法在 8.0+ 会报 ERROR 3685: Illegal argument to a regular expression，必须改成 \\b。

（5）适用场景

4.2 可执行 Shell 示例

示例 4-1：运算符与 NULL 三值逻辑验证

# ══════════════════════════════════════════════════════════════════════
# 示例 4-1 运算符与 NULL 语义验证
# 场景：新同事写的 WHERE 条件查不到数据，先跑一遍语义基准确认是不是 NULL 的锅
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 算术运算：除数为 0 返回 NULL，字符串隐式转数字（非数字开头一律转 0）
q "SELECT 1+2 AS 加, 2-1 AS 减, 2*3 AS 乘, 4/2 AS 除, 7%2 AS 取余, 4/0 AS 除零返回NULL;"

# ② 比较运算：任一操作数为 NULL 结果就是 NULL；2='2' 会自动转数字
q "SELECT 2=4 AS a, 2='2' AS b, 'e'='e' AS c, (2+2)=(3+1) AS d, 'r'=NULL AS e;"

# ③ 空安全等于 <=> ：NULL <=> NULL 返回 1，这是判断两值「真正相等」的唯一正确写法
q "SELECT NULL=NULL AS 普通等于, NULL<=>NULL AS 空安全等于, 1<=>1 AS 相等, 1<=>2 AS 不等;"

# ④ 逻辑运算三值表：AND 遇 0 得 0，OR 遇 1 得 1，其余含 NULL 则为 NULL
q "SELECT 2 AND 3 AS a, 4&&0 AS b, 0&&NULL AS c, 1 AND NULL AS d,
2 OR 3 AS e, 0 OR NULL AS f, 2 XOR 3 AS g, 1 XOR NULL AS h;"

# ⑤ 位运算：权限掩码实战（1=读 2=写 4=执行），判断某用户是否有「写」权限
q "SELECT 5 & 2 AS 是否有写权限, 5 | 2 AS 加上写权限, 5 & ~2 AS 去掉写权限, 1 << 3 AS 执行位;"

# ⑥ NULL vs 空值：COUNT(col) 忽略 NULL 但计入空串 —— 报表口径差异的根源
q "SELECT COUNT(*) AS 总行数, COUNT(coin) AS coin非NULL数,
SUM(coin IS NULL) AS coin为NULL数, SUM(coin = '') AS coin为空串数
FROM player;"

# ⑦ 最值函数遇 NULL 直接返回 NULL（不是忽略！），用 IFNULL 兜底
q "SELECT GREATEST(1,2,3) AS g1, GREATEST(1,NULL,3) AS 遇NULL返回NULL,
GREATEST(1,IFNULL(NULL,0),3) AS 用IFNULL兜底;"

示例 4-2：正则匹配与数据清洗（含 8.0 引擎变更修正）

# ══════════════════════════════════════════════════════════════════════
# 示例 4-2 正则匹配与生产数据清洗
# 场景：清洗玩家昵称中的异常字符，并校验订单备注里的手机号格式
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 基础正则：开头 ^ / 结尾 $ / 任意单字符 . / 或 |
q "SELECT id, name FROM player WHERE name REGEXP '^nb';" # 以 nb 开头
q "SELECT id, name FROM player WHERE name REGEXP 'ss\\$';" # 以 ss 结尾（\\$ 转义，避免被 shell 吞掉）
q "SELECT id, name FROM player WHERE name REGEXP 'shir.ey';" # shir 与 ey 之间隔 1 个字符
q "SELECT id, name FROM player WHERE name REGEXP 'on|in';" # 含 on 或 in

# ② 量词：* 前一字符 0+ 次；+ 前一字符 1+ 次；{n,m} 次数区间
q "SELECT id, name FROM player WHERE name REGEXP 'oooo*';" # 连续 3 个及以上 o
q "SELECT id, name FROM player WHERE name REGEXP 'oooo+';" # 连续 4 个及以上 o
q "SELECT id, name FROM player WHERE name REGEXP '^[d-f]';" # 以 d/e/f 开头
q "SELECT id, name FROM player WHERE name REGEXP '[^d-f]';" # 包含 d/e/f 之外的字符

# ③ ⚠ 8.0 引擎变更：[[:<:]] 词边界已废弃，改用 \\b
"${MYSQL[@]}" -D "$DB" -e "SELECT 'hello world' REGEXP '[[:<:]]world[[:>:]]';" 2>&1 \\
| grep -E 'ERROR|3685' && echo "↑ 已确认：8.0+ 移除 [[:<:]] 词边界，请改用 \\\\b" || true
q "SELECT 'hello world' REGEXP '\\\\\\\\bworld\\\\\\\\b' AS 用反斜杠b替代词边界;"

# ④ 8.0 新增正则函数：REGEXP_LIKE 做格式校验（比 LIKE 表达力强）
q "SELECT '13812345678' REGEXP '^1[3-9][0-9]{9}\\$' AS 合法手机号,
'12345' REGEXP '^1[3-9][0-9]{9}\\$' AS 非法手机号;"

# ⑤ 数据清洗：REGEXP_REPLACE 去掉昵称里的控制字符与多余空白（8.0+）
q "SELECT REGEXP_REPLACE(' ab\\\\tcd\\\\n ', '[[:space:]]+', ' ') AS 清洗空白,
REGEXP_REPLACE('abc123def', '[0-9]+', '***') AS 数字打码;"

# ⑥ REGEXP_SUBSTR 从文本里抽取结构化信息（例如从备注里抓手机号）
q "SELECT REGEXP_SUBSTR('联系人：13812345678 请尽快', '1[3-9][0-9]{9}') AS 抽取手机号;"

# ⑦ 生产注意：REGEXP 无法使用索引，用 EXPLAIN 确认它走的是全表扫描
q "EXPLAIN SELECT id FROM player WHERE name REGEXP '^nb'\\G"
echo "结论：type=ALL 即全表扫描。高频搜索场景必须改用 FULLTEXT（见第 13 章）或加冗余列 + LIKE 前缀。"

第 5 章 常用内置函数（数学 / 聚合 / 字符串 / 日期时间）

5.1 语法说明与适用场景

（1）数学函数（沿用讲义表 5-7）

（2）聚合函数（沿用讲义表 5-8）

（3）字符串函数（沿用讲义表 5-9）

（4）日期时间函数（沿用讲义表 5-10）

（5）适用场景对比

5.2 可执行 Shell 示例

示例 5-1：按月营收报表（含小计与环比）

# ══════════════════════════════════════════════════════════════════════
# 示例 5-1 报表查询：按月营收统计 + 小计 + 金额口径核对
# 场景：每月 1 号给老板出上月营收报表，需含订单数、GMV、客单价
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ───────────────────────────────══════════════════════════─
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 按月聚合：DATE_FORMAT 提取年月，SUM 求 GMV，ROUND 保留 2 位
q "SELECT DATE_FORMAT(created_at, '%Y-%m') AS 月份,
COUNT(*) AS 订单数,
ROUND(SUM(amount), 2) AS GMV,
ROUND(AVG(amount), 2) AS 客单价,
ROUND(MIN(amount), 2) AS 最低,
ROUND(MAX(amount), 2) AS 最高
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY 月份;"

# ② 带合计行：WITH ROLLUP + IFNULL 美化分组键
q "SELECT IFNULL(DATE_FORMAT(created_at,'%Y-%m'), '合计') AS 月份,
COUNT(*) AS 订单数, ROUND(SUM(amount), 2) AS GMV
FROM orders GROUP BY DATE_FORMAT(created_at,'%Y-%m') WITH ROLLUP;"

# ③ 分状态汇总：行转列写法（SUM + CASE WHEN），一次出多维报表
q "SELECT DATE_FORMAT(created_at,'%Y-%m') AS 月份,
SUM(status = 0) AS 待支付,
SUM(status = 1) AS 已支付,
SUM(status = 2) AS 已取消,
ROUND(SUM(CASE WHEN status = 1 THEN amount ELSE 0 END), 2) AS 已支付GMV
FROM orders GROUP BY 月份 ORDER BY 月份;"

# ④ 口径核对：count(*) vs count(col) vs count(distinct) 三者差异必须说清楚
q "SELECT COUNT(*) AS 总行数,
COUNT(user_id) AS user_id非空行数,
COUNT(DISTINCT user_id) AS 去重用户数
FROM orders;"

# ⑤ 分组拼接：把每个用户最近 5 个订单号拼成一行（先扩大 group_concat_max_len 防截断）
"${MYSQL[@]}" -D "$DB" -e "SET SESSION group_concat_max_len = 102400;" # 会话级，不影响全局
q "SELECT user_id, COUNT(*) AS 订单数,
GROUP_CONCAT(order_id ORDER BY order_id DESC SEPARATOR '|') AS 订单号列表
FROM orders GROUP BY user_id HAVING 订单数 >= 2 LIMIT 5;"

# ⑥ 日期维度分析：按星期几看下单分布（dayofweek：1=周日 2=周一 … 7=周六）
q "SELECT DAYOFWEEK(created_at) AS 星期编号,
CASE DAYOFWEEK(created_at) WHEN 1 THEN '周日' WHEN 2 THEN '周一' WHEN 3 THEN '周二'
WHEN 4 THEN '周三' WHEN 5 THEN '周四' WHEN 6 THEN '周五' ELSE '周六' END AS 星期,
COUNT(*) AS 订单数
FROM orders GROUP BY DAYOFWEEK(created_at) ORDER BY 星期编号;"

示例 5-2：字符串清洗与批量数据订正

# ══════════════════════════════════════════════════════════════════════
# 示例 5-2 数据清洗：脏字符串订正 + 批量 UPDATE 的安全流程
# 场景：历史数据里 nick 字段混入了前后空格和全角空格，需要批量清洗
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ─────────────────────────────────────════════════════════─
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 先「看见」问题数据：LENGTH vs CHAR_LENGTH 能看出是否含多字节/隐藏字符
q "SELECT id, name, LENGTH(name) AS 字节数, CHAR_LENGTH(name) AS 字符数
FROM player WHERE name <> TRIM(name) OR name LIKE '% %' LIMIT 10;"

# ② 清洗方案：TRIM 去空格 + REGEXP_REPLACE 去控制字符（8.0+）
q "SELECT name AS 原值,
REGEXP_REPLACE(TRIM(name), '[[:cntrl:]]', '') AS 清洗后
FROM player WHERE name REGEXP '[[:cntrl:]]|[[:space:]]' LIMIT 10;"

# ③ 批量更新「铁律三步」：① 先 SELECT 看量 → ② 备份/记录 → ③ 再 UPDATE
# 第一步：统计影响范围
q "SELECT COUNT(*) AS 待清洗行数 FROM player
WHERE name <> REGEXP_REPLACE(TRIM(name), '[[:cntrl:]]', '');"

# ④ 第二步：先备份到影子表（运维变更必备的回滚依据）
"${MYSQL[@]}" -D "$DB" -e "DROP TABLE IF EXISTS player_bak;
CREATE TABLE player_bak AS SELECT * FROM player;"
echo "已备份到 player_bak（$( "${MYSQL[@]}" -D "$DB" -N -B -e "SELECT COUNT(*) FROM player_bak;" ) 行）"

# ⑤ 第三步：执行更新（MySQL 不允许直接 UPDATE 本表同时子查询本表，此处不涉及故可直接改）
"${MYSQL[@]}" -D "$DB" -e "
UPDATE player
SET name = REGEXP_REPLACE(TRIM(name), '[[:cntrl:]]', '')
WHERE name <> REGEXP_REPLACE(TRIM(name), '[[:cntrl:]]', '');"
echo "清洗完成，已更新"

# ⑥ 验证：影响行数应为 0 才算彻底清洗干净
q "SELECT COUNT(*) AS 残留脏数据 FROM player
WHERE name <> REGEXP_REPLACE(TRIM(name), '[[:cntrl:]]', '');"

# ⑦ 误操作回滚（真出问题时用）：从影子表恢复
# "${MYSQL[@]}" -D "$DB" -e "UPDATE player p JOIN player_bak b ON p.id = b.id SET p.name = b.name;"
echo "回滚命令已备好（注释状态），需要时取消注释执行"

第 6 章 CTE 公共表表达式

讲义无此章节（MySQL 8.0 新增特性），依据 MySQL 8.0 / 8.4 官方标准实践补充。

6.1 语法说明与适用场景

（1）核心语法

— ① 非递归 CTE：把子查询提到前面命名，主查询可多次引用
WITH cte_name [(col1, col2, ...)] AS (
SELECT ...
)
[, cte_name2 AS ( ... )] — 多个 CTE 用逗号分隔，后者可引用前者
SELECT ... FROM cte_name ...;

— ② 递归 CTE：必须有锚成员（起点）+ 递归成员（UNION ALL 引用自身）+ 终止条件
WITH RECURSIVE cte_name (cols) AS (
SELECT 初始行 WHERE ... — 锚成员（Anchor）
UNION ALL
SELECT 下一层 FROM cte_name JOIN tbl ON ... — 递归成员，必须引用 cte 自身
WHERE 终止条件 — 缺了会撞上 cte_max_recursion_depth
)
SELECT ... FROM cte_name;

（2）CTE vs 派生表 vs 临时表

MySQL 8.0 的性能说明：8.0 早期 CTE 总是被物化（materialized），可能比等价派生表更慢。
8.0 起优化器支持 CTE 合并（merge），可用优化器 Hint 强制控制：
WITH cte AS (SELECT … ) SELECT /*+ MERGE(cte) */ … / /*+ NO_MERGE(cte) */（Hint 8.0.20+ 支持 CTE 名）。
CTE 被引用多次时必定物化，这是它的固定代价。

（3）关键变量

调大方式：SET SESSION cte_max_recursion_depth = 10000;（会话级，不改全局，避免影响其他连接）。

（4）适用场景

（5）⚠ 注意事项

6.2 可执行 Shell 示例

示例 6-1：部门树递归展开（组织架构报表）

# ══════════════════════════════════════════════════════════════════════
# 示例 6-1 递归 CTE：组织架构树展开
# 场景：HR 要一份「每个部门从顶层到本级的完整路径」报表，层级深度不定
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 确认部门树数据
q "SELECT * FROM dept ORDER BY parent_id, dept_id;"

# ② 递归展开：锚成员取根节点（parent_id IS NULL），递归成员逐层下钻，拼出路径
# 注意：锚成员里 CAST 预留了 500 字符宽度，否则递归拼接会被截断
q "WITH RECURSIVE dept_tree (dept_id, dept_name, parent_id, lvl, path) AS (
SELECT dept_id, dept_name, parent_id, 1,
CAST(dept_name AS CHAR(500)) — 锚成员：起点 + 预留路径宽度
FROM dept WHERE parent_id IS NULL
UNION ALL
SELECT d.dept_id, d.dept_name, d.parent_id, t.lvl + 1,
CONCAT(t.path, ' > ', d.dept_name) — 递归成员：引用 CTE 自身
FROM dept d
JOIN dept_tree t ON d.parent_id = t.dept_id
WHERE t.lvl < 10 — 终止条件：防御环路导致的无限递归
)
SELECT dept_id, lvl AS 层级, dept_name, path AS 完整路径
FROM dept_tree ORDER BY path;"

# ③ 按子树统计：每个部门（含所有子部门）下的员工数与薪资总额
q "WITH RECURSIVE sub (dept_id, root_id) AS (
SELECT dept_id, dept_id FROM dept — 锚：每个部门自身
UNION ALL
SELECT d.dept_id, s.root_id FROM dept d JOIN sub s ON d.parent_id = s.dept_id
)
SELECT s.root_id AS 部门ID,
(SELECT dept_name FROM dept WHERE dept_id = s.root_id) AS 部门名,
COUNT(e.emp_no) AS 含子部门员工数,
ROUND(SUM(e.salary), 2) AS 薪资合计
FROM sub s
LEFT JOIN emp e ON e.dept = (SELECT dept_name FROM dept WHERE dept_id = s.dept_id)
GROUP BY s.root_id ORDER BY 部门ID;"

# ④ 递归层数上限演示：超过 cte_max_recursion_depth 会报 ERROR 3636
q "SELECT @@cte_max_recursion_depth AS 默认递归上限;"
"${MYSQL[@]}" -D "$DB" -e "
SET SESSION cte_max_recursion_depth = 20; — 会话级调小，快速复现
WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 100)
SELECT COUNT(*) FROM seq;" 2>&1 | grep -E 'ERROR|3636' || echo "（未复现，检查 MySQL 版本）"

# ⑤ 正确做法：需要更多层数时，会话级调大（不要改全局，避免影响其他连接）
q "SET SESSION cte_max_recursion_depth = 10000;
WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 5000)
SELECT COUNT(*) AS 生成行数 FROM seq;"

示例 6-2：生成日期序列补零 + CTE 圈定 ID 做批量修复

# ══════════════════════════════════════════════════════════════════════
# 示例 6-2 日期序列补零报表 + 用 CTE 安全圈定待修复数据
# 场景：① 出「近 7 天每日订单数」报表，断更的日期要显示 0
# ② 把「30 天前的已取消订单」归档删除，先用 CTE 圈定范围再动手
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ─────────────────────────────────────════════════════════─
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 递归生成最近 7 天日期序列（补零报表的前提：先有完整日期轴）
q "WITH RECURSIVE days(d) AS (
SELECT CURDATE() – INTERVAL 6 DAY — 锚：7 天前的日期
UNION ALL
SELECT d + INTERVAL 1 DAY FROM days WHERE d < CURDATE() — 递归：逐天 +1，到今天停止
)
SELECT d AS 日期 FROM days ORDER BY d;"

# ② 日期轴 LEFT JOIN 订单表：没有订单的日期显示 0（COALESCE 把 NULL 转 0）
q "WITH RECURSIVE days(d) AS (
SELECT CURDATE() – INTERVAL 6 DAY
UNION ALL SELECT d + INTERVAL 1 DAY FROM days WHERE d < CURDATE()
)
SELECT days.d AS 日期,
COALESCE(o.订单数, 0) AS 订单数,
COALESCE(o.GMV, 0) AS GMV
FROM days
LEFT JOIN (
SELECT DATE(created_at) AS d, COUNT(*) AS 订单数, ROUND(SUM(amount),2) AS GMV
FROM orders GROUP BY DATE(created_at)
) o ON o.d = days.d
ORDER BY days.d;"

# ③ 数据修复场景：先用 CTE 圈定「30 天前已取消的订单」，只 SELECT 不动手
q "WITH victims AS (
SELECT order_id, amount, created_at
FROM orders
WHERE status = 2 AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
)
SELECT COUNT(*) AS 待归档订单数, ROUND(SUM(amount),2) AS 涉及金额 FROM victims;"

# ④ 确认无误后再改成 DELETE（多表删除语法：DELETE t FROM t JOIN cte）
# ⚠ 生产环境务必先备份，且分批 LIMIT 提交，避免长事务锁表
"${MYSQL[@]}" -D "$DB" -e "
CREATE TABLE IF NOT EXISTS orders_archive AS SELECT * FROM orders WHERE 1=0;" # 建空归档表（结构复制）
"${MYSQL[@]}" -D "$DB" -e "
WITH victims AS (
SELECT order_id FROM orders
WHERE status = 2 AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
)
INSERT INTO orders_archive SELECT o.* FROM orders o JOIN victims v ON o.order_id = v.order_id;"
echo "已归档到 orders_archive"

# ⑤ 确认归档成功后再删源数据（此处演示，实际按审批流程执行）
"${MYSQL[@]}" -D "$DB" -e "
WITH victims AS (
SELECT order_id FROM orders
WHERE status = 2 AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
)
DELETE o FROM orders o JOIN victims v ON o.order_id = v.order_id;"
echo "已从 orders 删除已归档数据"

# ⑥ 验证：源表该条件下的行数应为 0
q "SELECT COUNT(*) AS 残留待归档数 FROM orders
WHERE status = 2 AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);"

第 7 章 窗口函数

讲义无此章节（MySQL 8.0 新增特性），依据 MySQL 8.0 / 8.4 官方标准实践补充。

7.1 语法说明与适用场景

（1）核心语法

函数名([参数]) OVER (
[PARTITION BY 分组列] — 窗口内再分区，不写则整表一个窗口
[ORDER BY 排序列 [ASC|DESC]] — 窗口内排序，决定累计/排名的顺序
[帧子句 frame_clause] — 限定窗口内的计算范围
)
SELECT ... FROM ... WINDOW w AS (PARTITION BY ... ORDER BY ...) — 8.0.22+ 命名窗口

帧子句两种模式：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — 按「行」计数（推荐，行为确定）
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — 按「值」计数（默认，遇重复值有坑）
— 可用边界：UNBOUNDED PRECEDING / n PRECEDING / CURRENT ROW / n FOLLOWING / UNBOUNDED FOLLOWING

（2）函数分类与选型

（3）⚠ 窗口函数最大的三个坑

坑 1：窗口函数在 WHERE 之后执行，不能直接写进 WHERE

— ❌ 报错：WHERE 里不允许出现窗口函数
SELECT * FROM emp WHERE ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) = 1;
— ✅ 正确：套一层 CTE / 派生表，外层再过滤
WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM emp)
SELECT * FROM ranked WHERE rn = 1;

坑 2：默认帧是 RANGE … CURRENT ROW，遇重复值会把并列行一起算

— 默认 RANGE 帧下，相同 salary 的行会被归为同一组，SUM 结果是"并列行一起算"
SUM(salary) OVER (ORDER BY salary)
— 想要严格"截至当前行"的累计，必须显式写 ROWS：
SUM(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

坑 3：LAST_VALUE() 默认只看到当前行，取不到"最后一个值"

— ❌ 默认帧到 CURRENT ROW，LAST_VALUE 永远等于当前行的值
LAST_VALUE(salary) OVER (ORDER BY hire_date)
— ✅ 必须把帧终点推到最后一行
LAST_VALUE(salary) OVER (ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

（4）适用场景对比

（5）性能注意

• 窗口函数需要按 PARTITION BY / ORDER BY 排序，若已有匹配索引（如 (dept, salary)），可省掉排序；否则 Extra 会出现 Using temporary; Using filesort。
• 窗口函数不会减少返回行数，全表结果集都会参与计算，大表务必先用 WHERE 缩范围。

7.2 可执行 Shell 示例

示例 7-1：部门薪资排名、累计求和与环比

# ══════════════════════════════════════════════════════════════════════
# 示例 7-1 窗口函数实战：TopN-per-group / 累计求和 / 移动平均 / 环比
# 场景：HR 要「各部门薪资前三名 + 部门薪资占比 + 入职顺序累计薪资」报表
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 三种排名函数对比：并列时 RANK 跳号、DENSE_RANK 不跳号、ROW_NUMBER 连续唯一
q "SELECT dept, emp_name, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS 行号,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS 排名跳号,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS 排名不跳号
FROM emp ORDER BY dept, salary DESC;"

# ② 每个部门薪资 Top2（经典 TopN-per-group）：窗口函数必须套一层才能过滤
q "WITH ranked AS (
SELECT dept, emp_name, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM emp
)
SELECT dept, emp_name, salary, rn FROM ranked WHERE rn <= 2 ORDER BY dept, rn;"

# ③ 占部门总薪资比例：聚合窗口函数 SUM() OVER (PARTITION BY dept) 不压缩行
q "SELECT dept, emp_name, salary,
SUM(salary) OVER (PARTITION BY dept) AS 部门薪资合计,
ROUND(salary / SUM(salary) OVER (PARTITION BY dept) * 100, 2) AS 占比百分比
FROM emp ORDER BY dept, salary DESC;"

# ④ 累计求和：必须显式写 ROWS 帧，否则默认 RANGE 帧遇到相同 salary 会并列计算
q "SELECT dept, emp_name, salary,
SUM(salary) OVER (ORDER BY hire_date, emp_no
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 按入职累计薪资
FROM emp ORDER BY hire_date, emp_no;"

# ⑤ 移动平均：最近 3 条记录的滑动平均
q "SELECT emp_name, hire_date, salary,
ROUND(AVG(salary) OVER (ORDER BY hire_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS 近3人移动平均
FROM emp ORDER BY hire_date;"

# ⑥ LAG/LEAD 做环比：与上一条记录的差值
q "SELECT emp_name, dept, salary,
LAG(salary, 1) OVER (PARTITION BY dept ORDER BY salary DESC) AS 上一名薪资,
salary – LAG(salary, 1) OVER (PARTITION BY dept ORDER BY salary DESC) AS 与上一名差值
FROM emp ORDER BY dept, salary DESC;"

# ⑦ ⚠ LAST_VALUE 的默认帧陷阱验证：不写帧子句时，LAST_VALUE 等于当前行
q "SELECT emp_name, salary,
LAST_VALUE(salary) OVER (ORDER BY hire_date) AS 默认帧_等于当前行,
LAST_VALUE(salary) OVER (ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 显式全帧_真正最后值
FROM emp ORDER BY hire_date LIMIT 6;"

# ⑧ 8.0.22+ 命名窗口：多个窗口函数复用同一窗口定义，减少重复书写
q "SELECT dept, emp_name, salary,
ROW_NUMBER() OVER w AS 行号,
SUM(salary) OVER w AS 累计薪资
FROM emp WINDOW w AS (PARTITION BY dept ORDER BY salary DESC)
ORDER BY dept, salary DESC LIMIT 10;"

示例 7-2：去重取最新一条 + 连续登录天数（gaps and islands）

# ══════════════════════════════════════════════════════════════════════
# 示例 7-2 窗口函数进阶：去重取最新 + 连续区间问题
# 场景：① 每个用户只保留最近一笔订单（数据去重）
# ② 统计每个用户的最长连续下单天数（运营留存分析）
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 每个用户最近一笔订单：ROW_NUMBER 按 user_id 分区、时间倒序，取 rn=1
q "WITH latest AS (
SELECT order_id, user_id, amount, created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, order_id DESC) AS rn
FROM orders
)
SELECT user_id, order_id, amount, created_at
FROM latest WHERE rn = 1 ORDER BY user_id LIMIT 10;"

# ② 对比传统写法（GROUP BY + 自连接）：语义相同但更啰嗦，且取值受 ONLY_FULL_GROUP_BY 限制
q "SELECT o.user_id, o.order_id, o.amount, o.created_at
FROM orders o
JOIN (SELECT user_id, MAX(created_at) AS mx FROM orders GROUP BY user_id) m
ON o.user_id = m.user_id AND o.created_at = m.mx
ORDER BY o.user_id LIMIT 10;"

# ③ 每个用户订单的「上一单间隔天数」：LAG 按用户分区按时间排序
q "WITH t AS (
SELECT user_id, order_id, created_at,
LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_at
FROM orders
)
SELECT user_id, order_id, created_at, prev_at,
DATEDIFF(created_at, prev_at) AS 距上单天数
FROM t WHERE prev_at IS NOT NULL ORDER BY user_id, created_at LIMIT 10;"

# ④ 连续区间（gaps and islands）核心技巧：日期序号 – 行序号 = 常数 ⇒ 同一连续段
# 第 1 步：每个用户按天去重并编号
q "WITH daily AS (
SELECT DISTINCT user_id, DATE(created_at) AS d,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY DATE(created_at)) AS rn
FROM orders WHERE user_id IS NOT NULL
),
grp AS (
SELECT user_id, d, rn, DATE_SUB(d, INTERVAL rn DAY) AS grp_key
FROM daily
)
SELECT user_id, MIN(d) AS 连续段起始, MAX(d) AS 连续段结束,
DATEDIFF(MAX(d), MIN(d)) + 1 AS 连续天数
FROM grp GROUP BY user_id, grp_key
ORDER BY 连续天数 DESC, user_id LIMIT 10;"

# ⑤ 用 NTILE 做用户价值分层：按消费总额分成 4 档（RFM 的 M 维度）
q "WITH user_amt AS (
SELECT user_id, SUM(amount) AS total FROM orders WHERE user_id IS NOT NULL GROUP BY user_id
)
SELECT user_id, ROUND(total, 2) AS 消费总额,
NTILE(4) OVER (ORDER BY total DESC) AS 价值档位 — 1 为最高价值
FROM user_amt ORDER BY 价值档位, total DESC LIMIT 12;"

# ⑥ 性能验证：确认 PARTITION BY + ORDER BY 命中了 emp 的 idx_dept_salary 索引
q "EXPLAIN SELECT dept, emp_name,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM emp\\G"
echo "若 Extra 里出现 Using temporary / Using filesort，说明排序未能被索引消除，需补 (dept, salary) 复合索引。"

第 8 章 视图

讲义无此章节，依据 MySQL 8.0 / 8.4 官方标准实践补充。

8.1 语法说明与适用场景

（1）核心语法

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = user]
[SQL SECURITY {DEFINER | INVOKER}]
VIEW view_name [(col_list)] AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];

DROP VIEW [IF EXISTS] view_name [, view_name2] ...;
SHOW CREATE VIEW view_name\\G

（2）三种算法对比（决定性能与可更新性）

判定：含 GROUP BY / DISTINCT / 聚合函数 / UNION 的视图，MySQL 只能用 TEMPTABLE，因此这类视图做 WHERE 过滤时会先把全量数据物化，再在临时表上过滤——性能陷阱。
对策：对这类视图的过滤条件，应直接下推到基表查询，或改用「参数化视图 = 存储过程」。

（3）WITH CHECK OPTION 的作用

典型用途：创建"只看本部门数据"的视图并加 CHECK OPTION，防止业务账号通过视图往其他部门写入。

（4）可更新视图的限制

以下结构一律不可更新（INSERT/UPDATE/DELETE 会报错）：聚合函数、DISTINCT、GROUP BY、HAVING、UNION、子查询（在 SELECT 列表或 WHERE 中引用基表之外的表）、JOIN（多表可更新视图有限制）、ALGORITHM=TEMPTABLE。

（5）适用场景

（6）⚠ 注意事项

8.2 可执行 Shell 示例

示例 8-1：建只读报表视图并用它做权限隔离

# ══════════════════════════════════════════════════════════════════════
# 示例 8-1 视图：报表封装 + CHECK OPTION 防越权 + 权限隔离
# 场景：给运营同学开一个只读账号，只能看「已支付订单」，且不能看到用户手机号等敏感列
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 建可更新的 MERGE 视图：只暴露已支付订单，并用 CHECK OPTION 锁死写入范围
"${MYSQL[@]}" -D "$DB" <<'SQL'
CREATE OR REPLACE
ALGORITHM = MERGE — 强制合并算法，保证 WHERE 能下推到基表走索引
SQL SECURITY DEFINER — 以定义者权限访问基表，调用者无需基表权限
VIEW v_paid_orders AS
SELECT order_id, user_id, amount, created_at
FROM orders
WHERE status = 1
WITH CASCADED CHECK OPTION; — 任何通过该视图的写入都必须满足 status = 1
SQL
echo "视图 v_paid_orders 已创建"

# ② 验证 CHECK OPTION：尝试插入一条 status=0 的数据，应被拒绝
"${MYSQL[@]}" -D "$DB" -e "INSERT INTO v_paid_orders (user_id, amount, status) VALUES (999, 1.00, 0);" 2>&1 \\
| grep -E 'ERROR|1369|CHECK' || echo "（若表无 status 列暴露则此步不适用，见 ③）"

# ③ 正确的拒绝演示：视图未暴露 status 列，插入时 status 取默认值 0，触发 CHECK OPTION
q "SELECT COUNT(*) AS 视图行数 FROM v_paid_orders;"

# ④ 查看视图定义与算法（运维排查视图性能问题的第一步）
q "SHOW CREATE VIEW v_paid_orders\\G"

# ⑤ 验证 MERGE 算法确实把条件下推到基表：EXPLAIN 应显示使用 idx_status_amount 索引
q "EXPLAIN SELECT * FROM v_paid_orders WHERE amount > 500\\G"

# ⑥ 对比：含 GROUP BY 的视图只能走 TEMPTABLE，无法下推条件
"${MYSQL[@]}" -D "$DB" <<'SQL'
CREATE OR REPLACE VIEW v_dept_summary AS
SELECT dept, COUNT(*) AS headcount, ROUND(SUM(salary),2) AS total_salary
FROM emp GROUP BY dept;
SQL
q "SHOW CREATE VIEW v_dept_summary\\G" # ALGORITHM 会显示为 UNDEFINED/TEMPTABLE
q "EXPLAIN SELECT * FROM v_dept_summary WHERE dept = '技术部'\\G" # 注意：dept 过滤无法下推到基表索引

# ⑦ 权限隔离：建只读账号，只授予视图权限（不给基表任何权限）
"${MYSQL[@]}" <<'SQL'
CREATE USER IF NOT EXISTS 'report_ro'@'%' IDENTIFIED BY 'Report@2026!';
GRANT SELECT ON demo.v_paid_orders TO 'report_ro'@'%';
FLUSH PRIVILEGES;
SQL
q "SHOW GRANTS FOR 'report_ro'@'%';"

# ⑧ 用新账号验证：能查视图，但查基表应被拒绝
mysql -u report_ro -p'Report@2026!' -D "$DB" -t -e "SELECT COUNT(*) AS 可见订单数 FROM v_paid_orders;" 2>/dev/null
mysql -u report_ro -p'Report@2026!' -D "$DB" -e "SELECT COUNT(*) FROM orders;" 2>&1 \\
| grep -E 'ERROR|1142|denied' && echo "↑ 权限隔离生效：无法直连基表" || echo "（账号已存在且权限不同，请按实际调整）"

# ⑨ 清理（按需）：删除演示账号与视图
# "${MYSQL[@]}" -e "DROP USER IF EXISTS 'report_ro'@'%'; DROP VIEW IF EXISTS demo.v_paid_orders, demo.v_dept_summary;"
echo "清理命令已备好（注释状态）"

第 9 章 存储过程与存储函数

9.1 语法说明与适用场景

（1）核心语法（沿用讲义 5.3）

— ① 分隔符切换：过程体里有多条分号结尾的 SQL，必须先把语句结束符换成 $$
DELIMITER $$
CREATE PROCEDURE proc_name ([IN|OUT|INOUT] 参数名 类型 [, ...])
[特性 ...]
BEGIN
— 声明顺序是硬约束：变量 → 条件 → 游标 → 处理器（顺序错了直接报 1064）
DECLARE 变量名 类型 [DEFAULT 值];
DECLARE 游标名 CURSOR FOR SELECT ...;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
— 业务逻辑
END$$
DELIMITER ; — 注意分号前有空格，改回默认结束符

— ② 调用
CALL proc_name(@out_param);
SELECT @out_param;

— ③ 存储函数
CREATE FUNCTION func_name(参数 类型) RETURNS 返回类型
[DETERMINISTIC | NO SQL | READS SQL DATA] — 必须声明其一，否则开启 binlog 时报错
BEGIN ... RETURN 值; END$$

（2）参数模式

（3）流程控制

（4）⚠ 高频踩坑（本节最重要）

（5）适用场景

工程建议：课程作业中存储过程是重点考察项；真实生产中，把存储过程限制在运维类任务（归档、清理、批量订正），业务逻辑尽量留在应用层。

9.2 可执行 Shell 示例

示例 9-1：批量归档历史订单（带 OUT 参数与事务）

# ══════════════════════════════════════════════════════════════════════
# 示例 9-1 存储过程：历史订单归档（OUT 参数 + 事务 + 分批提交）
# 场景：每月把 90 天前的已取消订单归档到 orders_archive，并从主表删除
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'

# ① 建归档表（结构复制，不复制数据）—— 幂等：IF NOT EXISTS
"${MYSQL[@]}" -D "$DB" -e "CREATE TABLE IF NOT EXISTS orders_archive AS SELECT * FROM orders WHERE 1=0;"

# ② 创建存储过程：IN 参数指定保留天数，OUT 参数返回实际归档行数
# ⚠ 关键：用 heredoc 的 'SQL' 引号形式，避免 shell 展开 $ 和反引号
"${MYSQL[@]}" -D "$DB" <<'SQL'
DROP PROCEDURE IF EXISTS sp_archive_orders; — 幂等：先删除再重建
DELIMITER $$
CREATE PROCEDURE sp_archive_orders(
IN p_keep_days INT, — 输入：保留最近 N 天，更早的归档
IN p_batch_size INT, — 输入：单批行数，防止长事务
OUT p_archived INT — 输出：实际归档的行数
)
BEGIN
DECLARE v_done INT DEFAULT 0; — ① 必须最先声明变量
DECLARE v_batch INT DEFAULT 0; — 本批归档行数

DECLARE EXIT HANDLER FOR SQLEXCEPTION — ② 异常处理器：任何 SQL 错误都回滚
BEGIN
ROLLBACK;
SET p_archived = -1; — 用 -1 标记失败，便于调用方判断
END;

SET p_archived = 0;

— ③ 循环分批：每批 p_batch_size 行，直到没有符合条件的数据
archive_loop: LOOP
START TRANSACTION; — 每批独立事务，避免长事务锁表

INSERT INTO orders_archive
SELECT o.* FROM orders o
WHERE o.status = 2
AND o.created_at < DATE_SUB(NOW(), INTERVAL p_keep_days DAY)
LIMIT p_batch_size; — LIMIT 必须写常量或变量，不能写 LIMIT ? 占位符

SET v_batch = ROW_COUNT(); — 本批实际插入行数

DELETE FROM orders
WHERE status = 2
AND created_at < DATE_SUB(NOW(), INTERVAL p_keep_days DAY)
LIMIT p_batch_size;

COMMIT;
SET p_archived = p_archived + v_batch;

IF v_batch = 0 THEN — 本批为 0 说明数据处理完毕
LEAVE archive_loop; — LEAVE 退出 LOOP
END IF;
END LOOP archive_loop;
END$$
DELIMITER ;
SQL
echo "存储过程 sp_archive_orders 创建完成"

# ③ 查看已创建的存储过程（8.0 可用 information_schema.ROUTINES 精确过滤）
"${MYSQL[@]}" -D "$DB" -t -e "
SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = '${DB}' ORDER BY ROUTINE_TYPE, ROUTINE_NAME;"
"${MYSQL[@]}" -D "$DB" -e "SHOW CREATE PROCEDURE sp_archive_orders\\G" | head -20

# ④ 调用：IN 参数传值，OUT 参数必须用用户变量 @xxx 接收
"${MYSQL[@]}" -D "$DB" -e "CALL sp_archive_orders(90, 500, @archived); SELECT @archived AS 归档行数;"
# 返回 -1 表示执行中发生了异常并回滚

# ⑤ 验证结果：归档表新增多少，主表对应减少多少
"${MYSQL[@]}" -D "$DB" -t -e "
SELECT (SELECT COUNT(*) FROM orders_archive) AS 归档表行数,
(SELECT COUNT(*) FROM orders WHERE status = 2) AS 主表剩余已取消行数;"

示例 9-2：存储函数 + 游标逐行处理

# ══════════════════════════════════════════════════════════════════════
# 示例 9-2 存储函数与游标：格式校验函数 + 逐行处理的游标模板
# 场景：① 封装一个可复用的手机号校验函数
# ② 用游标逐行扫描并生成每个用户的汇总记录（演示 DECLARE 顺序铁律）
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'

# ① 创建存储函数：必须声明 DETERMINISTIC / NO SQL / READS SQL DATA 之一
# 否则开启 binlog 时报 ERROR 1418
"${MYSQL[@]}" -D "$DB" <<'SQL'
DROP FUNCTION IF EXISTS fn_is_mobile;
DELIMITER $$
CREATE FUNCTION fn_is_mobile(p_phone VARCHAR(32))
RETURNS TINYINT
DETERMINISTIC — 相同输入恒定相同输出，必须声明
BEGIN
IF p_phone IS NULL THEN — NULL 兜底：任何与 NULL 的比较都是 NULL
RETURN 0;
END IF;
RETURN p_phone REGEXP '^1[3-9][0-9]{9}$'; — 8.0 的 ICU 正则引擎
END$$
DELIMITER ;
SQL
echo "函数 fn_is_mobile 创建完成"

# ② 调用存储函数：可以像内置函数一样在 SQL 里直接用
"${MYSQL[@]}" -D "$DB" -t -e "
SELECT fn_is_mobile('13812345678') AS 合法,
fn_is_mobile('12345') AS 非法,
fn_is_mobile(NULL) AS NULL输入;"

# ③ 建汇总表（游标演示的目标表）
"${MYSQL[@]}" -D "$DB" -e "
CREATE TABLE IF NOT EXISTS user_summary (
user_id INT PRIMARY KEY,
order_cnt INT NOT NULL DEFAULT 0,
total_amt DECIMAL(12,2) NOT NULL DEFAULT 0,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;"

# ④ 游标模板：演示 DECLARE 的强制顺序（变量 → 条件 → 游标 → HANDLER）
"${MYSQL[@]}" -D "$DB" <<'SQL'
DROP PROCEDURE IF EXISTS sp_build_user_summary;
DELIMITER $$
CREATE PROCEDURE sp_build_user_summary()
BEGIN
— ⚠ DECLARE 顺序不可乱：变量 → 条件 → 游标 → HANDLER，且必须集中在 BEGIN 之后
DECLARE v_user_id INT; — ① 变量
DECLARE v_cnt INT DEFAULT 0;
DECLARE v_amt DECIMAL(12,2) DEFAULT 0;
DECLARE v_done TINYINT DEFAULT 0; — 游标结束标志

DECLARE cur_user CURSOR FOR — ② 游标
SELECT user_id, COUNT(*), SUM(amount)
FROM orders WHERE user_id IS NOT NULL
GROUP BY user_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND — ③ 处理器：必须配，否则报 1329
SET v_done = 1;

OPEN cur_user; — ④ 打开游标
read_loop: LOOP
FETCH cur_user INTO v_user_id, v_cnt, v_amt; — 取一行到变量
IF v_done = 1 THEN
LEAVE read_loop; — 取完退出
END IF;

INSERT INTO user_summary (user_id, order_cnt, total_amt)
VALUES (v_user_id, v_cnt, v_amt)
ON DUPLICATE KEY UPDATE — 幂等：重复执行只更新不报错
order_cnt = v_cnt, total_amt = v_amt, updated_at = NOW();
END LOOP;
CLOSE cur_user; — ⑤ 关闭游标
END$$
DELIMITER ;
SQL
echo "存储过程 sp_build_user_summary 创建完成"

# ⑤ 调用并验证（可重复执行，ON DUPLICATE KEY 保证幂等）
"${MYSQL[@]}" -D "$DB" -e "CALL sp_build_user_summary();"
"${MYSQL[@]}" -D "$DB" -t -e "SELECT * FROM user_summary ORDER BY total_amt DESC LIMIT 5;"

# ⑥ 说明：上面这个例子用 INSERT … SELECT 一条 SQL 就能搞定，游标是「演示语法」而非最优解
"${MYSQL[@]}" -D "$DB" -t -e "
SELECT '等价的高效写法：REPLACE INTO user_summary SELECT user_id, COUNT(*), SUM(amount), NOW()
FROM orders WHERE user_id IS NOT NULL GROUP BY user_id;' AS 优化建议;"

# ⑦ 清理
"${MYSQL[@]}" -D "$DB" -e "DROP PROCEDURE IF EXISTS sp_build_user_summary;"
echo "演示用游标过程已清理"

第 10 章 触发器

讲义无此章节，依据 MySQL 8.0 / 8.4 官方标准实践补充。

10.1 语法说明与适用场景

（1）核心语法

DELIMITER $$
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
[FOLLOWS | PRECEDES other_trigger] — 8.0 支持同事件多触发器并指定顺序
BEGIN
— NEW.col ：INSERT/UPDATE 时的「新值」（DELETE 无 NEW）
— OLD.col ：UPDATE/DELETE 时的「旧值」（INSERT 无 OLD）
END$$
DELIMITER ;

SHOW TRIGGERS [FROM db] [\\G]
DROP TRIGGER [IF EXISTS] trigger_name;

（2）六个触发时机与 NEW / OLD 可用性

MySQL 只有 FOR EACH ROW（行级触发器），不支持语句级触发器——这意味着批量更新 100 万行会触发 100 万次，写放大极其严重。

（3）适用场景对比

（4）⚠ 触发器的重要限制（必须记住）

10.2 可执行 Shell 示例

示例 10-1：订单审计触发器（记录变更前后完整快照）

# ══════════════════════════════════════════════════════════════════════
# 示例 10-1 触发器：订单变更审计 + 状态流转校验
# 场景：合规要求「订单金额与状态的每次变更都要留痕」，且不允许从已支付改回待支付
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 建审计表：记录变更前后的完整快照 + 操作类型 + 时间 + 操作者
"${MYSQL[@]}" -D "$DB" <<'SQL'
CREATE TABLE IF NOT EXISTS orders_audit (
audit_id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_id BIGINT NOT NULL,
action ENUM('INSERT','UPDATE','DELETE') NOT NULL,
old_status TINYINT,
new_status TINYINT,
old_amount DECIMAL(10,2),
new_amount DECIMAL(10,2),
changed_by VARCHAR(64) DEFAULT (CURRENT_USER()), — 记录执行账号，合规追溯
changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
KEY idx_order (order_id),
KEY idx_time (changed_at)
) ENGINE=InnoDB;
SQL
echo "审计表 orders_audit 就绪"

# ② INSERT 触发器：新增订单时落一条审计（此时只有 NEW，没有 OLD）
"${MYSQL[@]}" -D "$DB" <<'SQL'
DROP TRIGGER IF EXISTS trg_orders_ai;
DELIMITER $$
CREATE TRIGGER trg_orders_ai AFTER INSERT ON orders FOR EACH ROW
BEGIN
INSERT INTO orders_audit (order_id, action, new_status, new_amount)
VALUES (NEW.order_id, 'INSERT', NEW.status, NEW.amount);
END$$
DELIMITER ;
SQL
echo "INSERT 触发器已建"

# ③ UPDATE 触发器：同时记录 OLD 与 NEW，并校验状态流转合法性
"${MYSQL[@]}" -D "$DB" <<'SQL'
DROP TRIGGER IF EXISTS trg_orders_au;
DELIMITER $$
CREATE TRIGGER trg_orders_au BEFORE UPDATE ON orders FOR EACH ROW
BEGIN
— ① 业务规则：已支付(1) 不允许回退到待支付(0)，用 SIGNAL 抛错中断
IF OLD.status = 1 AND NEW.status = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '业务规则拒绝：已支付订单不能回退为待支付';
END IF;

— ② 金额不允许被改成负数（BEFORE 触发器可以修改 NEW 的值，这里是校验）
IF NEW.amount < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '业务规则拒绝：订单金额不能为负数';
END IF;
END$$
DELIMITER ;
SQL

# ④ AFTER UPDATE 触发器：落审计（OLD 与 NEW 都可用）
"${MYSQL[@]}" -D "$DB" <<'SQL'
DROP TRIGGER IF EXISTS trg_orders_au_log;
DELIMITER $$
CREATE TRIGGER trg_orders_au_log AFTER UPDATE ON orders FOR EACH ROW
BEGIN
— 只在真正发生变化时记录，避免无意义更新刷爆审计表
IF OLD.status <> NEW.status OR OLD.amount <> NEW.amount THEN
INSERT INTO orders_audit (order_id, action, old_status, new_status, old_amount, new_amount)
VALUES (NEW.order_id, 'UPDATE', OLD.status, NEW.status, OLD.amount, NEW.amount);
END IF;
END$$
DELIMITER ;
SQL
echo "UPDATE 触发器已建（含业务校验 + 审计）"

# ⑤ 验证触发器：正常更新应成功并落审计
"${MYSQL[@]}" -D "$DB" -e "
INSERT INTO orders (user_id, amount, status) VALUES (8888, 199.00, 0);
UPDATE orders SET status = 1 WHERE user_id = 8888 AND status = 0 LIMIT 1;"
q "SELECT order_id, action, old_status, new_status, old_amount, new_amount, changed_by
FROM orders_audit ORDER BY audit_id DESC LIMIT 5;"

# ⑥ 验证校验规则：把已支付改回待支付，应被 SIGNAL 拒绝
"${MYSQL[@]}" -D "$DB" -e "UPDATE orders SET status = 0 WHERE user_id = 8888 LIMIT 1;" 2>&1 \\
| grep -E 'ERROR|1644|业务规则拒绝' && echo "↑ 业务规则生效，非法状态流转已被拦截" || echo "（未触发，请检查触发器是否创建）"

# ⑦ 查看已建触发器（8.0 支持同事件多触发器，用 ACTION_ORDER 看执行顺序）
q "SELECT TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING, ACTION_ORDER
FROM information_schema.TRIGGERS WHERE EVENT_OBJECT_SCHEMA = '${DB}'
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, ACTION_ORDER;"

# ⑧ ⚠ 大批量导入前必须禁用触发器（行级触发器 = N 次额外写入）
q "SELECT '批量导入前：DROP TRIGGER trg_orders_au_log; 导入完成后再 CREATE 回来' AS 性能提示;"

# ⑨ 清理演示数据（触发器保留）
"${MYSQL[@]}" -D "$DB" -e "DELETE FROM orders WHERE user_id = 8888;"
echo "演示数据已清理"

示例 10-2：冗余统计表自动维护（AFTER 触发器）

# ══════════════════════════════════════════════════════════════════════
# 示例 10-2 触发器：自动维护冗余统计表，避免每次查全表
# 场景：首页要显示「各状态订单数」，数据量千万级，不能每次 COUNT(*)
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 建统计表并初始化（先全量算一次，之后靠触发器增量维护）
"${MYSQL[@]}" -D "$DB" <<'SQL'
DROP TABLE IF EXISTS orders_stat;
CREATE TABLE orders_stat (
status TINYINT PRIMARY KEY,
order_cnt BIGINT NOT NULL DEFAULT 0,
total_amt DECIMAL(14,2) NOT NULL DEFAULT 0
) ENGINE=InnoDB;

INSERT INTO orders_stat (status, order_cnt, total_amt)
SELECT status, COUNT(*), COALESCE(SUM(amount),0) FROM orders GROUP BY status;
SQL
q "SELECT * FROM orders_stat ORDER BY status;"

# ② INSERT 后 +1（ON DUPLICATE KEY 保证新状态自动建行）
"${MYSQL[@]}" -D "$DB" <<'SQL'
DROP TRIGGER IF EXISTS trg_orders_stat_ins;
DELIMITER $$
CREATE TRIGGER trg_orders_stat_ins AFTER INSERT ON orders FOR EACH ROW
BEGIN
INSERT INTO orders_stat (status, order_cnt, total_amt)
VALUES (NEW.status, 1, NEW.amount)
ON DUPLICATE KEY UPDATE
order_cnt = order_cnt + 1,
total_amt = total_amt + NEW.amount;
END$$
DELIMITER ;
SQL

# ③ DELETE 后 -1
"${MYSQL[@]}" -D "$DB" <<'SQL'
DROP TRIGGER IF EXISTS trg_orders_stat_del;
DELIMITER $$
CREATE TRIGGER trg_orders_stat_del AFTER DELETE ON orders FOR EACH ROW
BEGIN
UPDATE orders_stat
SET order_cnt = order_cnt – 1, total_amt = total_amt – OLD.amount
WHERE status = OLD.status;
END$$
DELIMITER ;
SQL

# ④ UPDATE 时按状态分组调整：先看状态是否变化，再决定加在哪、减在哪
"${MYSQL[@]}" -D "$DB" <<'SQL'
DROP TRIGGER IF EXISTS trg_orders_stat_upd;
DELIMITER $$
CREATE TRIGGER trg_orders_stat_upd AFTER UPDATE ON orders FOR EACH ROW
BEGIN
IF OLD.status = NEW.status THEN
— 状态没变，只调金额
UPDATE orders_stat SET total_amt = total_amt – OLD.amount + NEW.amount
WHERE status = NEW.status;
ELSE
— 状态变了：旧状态 -1，新状态 +1
UPDATE orders_stat SET order_cnt = order_cnt – 1, total_amt = total_amt – OLD.amount
WHERE status = OLD.status;
INSERT INTO orders_stat (status, order_cnt, total_amt)
VALUES (NEW.status, 1, NEW.amount)
ON DUPLICATE KEY UPDATE
order_cnt = order_cnt + 1, total_amt = total_amt + NEW.amount;
END IF;
END$$
DELIMITER ;
SQL
echo "统计表维护触发器已建（INSERT / UPDATE / DELETE 三个）"

# ⑤ 一致性验证：触发器维护的统计值 应 == 实时 COUNT/SUM 的结果
"${MYSQL[@]}" -D "$DB" -e "INSERT INTO orders (user_id, amount, status) VALUES (7777, 50.00, 0);"
q "SELECT s.status,
s.order_cnt AS 触发器统计,
(SELECT COUNT(*) FROM orders o WHERE o.status = s.status) AS 实际行数,
s.order_cnt = (SELECT COUNT(*) FROM orders o WHERE o.status = s.status) AS 是否一致
FROM orders_stat s ORDER BY s.status;"

# ⑥ 修复漂移：若发现不一致，用一条 SQL 重算（触发器的兜底方案，建议每天跑一次）
"${MYSQL[@]}" -D "$DB" -e "
TRUNCATE TABLE orders_stat;
INSERT INTO orders_stat (status, order_cnt, total_amt)
SELECT status, COUNT(*), COALESCE(SUM(amount),0) FROM orders GROUP BY status;"
q "SELECT '已全量重算统计表（建议配 crontab 每日低峰执行）' AS 说明;"

# ⑦ 清理
"${MYSQL[@]}" -D "$DB" -e "DELETE FROM orders WHERE user_id = 7777;"
echo "演示数据已清理"

第 11 章 事务与锁

讲义无此章节，依据 MySQL 8.0 / 8.4 官方标准实践补充。

11.1 语法说明与适用场景

（1）事务语法

START TRANSACTION [READ ONLY | READ WRITE] [WITH CONSISTENT SNAPSHOT];
— 或 BEGIN / BEGIN WORK（等价）
SAVEPOINT savepoint_name; — 设置保存点
ROLLBACK TO SAVEPOINT sp; — 回滚到保存点（事务不结束）
RELEASE SAVEPOINT sp;
COMMIT; — 提交
ROLLBACK; — 全部回滚

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};
SELECT @@transaction_isolation; — 8.0+ 变量名（5.7 是 @@tx_isolation）
SELECT @@autocommit;

WITH CONSISTENT SNAPSHOT：仅对 REPEATABLE READ 有效，在 START TRANSACTION 立即建立一致性视图（而不是等第一条 SQL）。mysqldump –single-transaction 正是用这个实现无锁备份。

（2）四种隔离级别对比

InnoDB 的 RR 已解决幻读：快照读靠 MVCC（readview），当前读靠 Next-Key Lock（行锁 + 间隙锁）锁住范围。这与 SQL 标准里"RR 允许幻读"不同，是 InnoDB 的实现优势。

（3）锁类型与排查入口

加锁读语法：

SELECT ... FOR UPDATE; — 排他锁（X 锁），其他事务不能读也不能改（当前读）
SELECT ... FOR SHARE; — 共享锁（S 锁），8.0+；等价于旧的 LOCK IN SHARE MODE
SELECT ... FOR UPDATE NOWAIT; — 8.0+：拿不到锁立即报错，不等待
SELECT ... FOR UPDATE SKIP LOCKED; — 8.0+：跳过被锁的行（队列场景神器）

（4）⚠ MySQL 8.4 的破坏性变更（本章重点）

（5）死锁与锁等待

死锁排查：SHOW ENGINE INNODB STATUS\\G → 找 LATEST DETECTED DEADLOCK 段（只保留最近一次）。
实时锁等待：SELECT * FROM sys.innodb_lock_waits;（sys 库视图，最直观）。

（6）适用场景

11.2 可执行 Shell 示例

示例 11-1：转账事务 + 保存点 + 隔离级别验证

# ══════════════════════════════════════════════════════════════════════
# 示例 11-1 事务：转账原子性 + SAVEPOINT 部分回滚 + 隔离级别验证
# 场景：账户间转账必须要么全成功要么全失败；中间步骤失败要能部分回滚
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ─────────────────────────────────────════════─────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 建账户表并初始化两个账户
"${MYSQL[@]}" -D "$DB" <<'SQL'
CREATE TABLE IF NOT EXISTS account (
id INT PRIMARY KEY,
name VARCHAR(32) NOT NULL,
balance DECIMAL(12,2) NOT NULL DEFAULT 0 CHECK (balance >= 0) — CHECK 约束 8.0.16+ 才真正生效
) ENGINE=InnoDB;
REPLACE INTO account (id, name, balance) VALUES (1,'张三',1000.00), (2,'李四',1000.00);
SQL
q "SELECT @@autocommit AS 自动提交状态, @@transaction_isolation AS 当前隔离级别;"
q "SELECT * FROM account;"

# ② 完整事务：转出 + 转入，两步都成功才 COMMIT
"${MYSQL[@]}" -D "$DB" <<'SQL'
START TRANSACTION; — ① 开启事务，关闭自动提交
UPDATE account SET balance = balance – 200 WHERE id = 1; — ② 张三 -200
UPDATE account SET balance = balance + 200 WHERE id = 2; — ③ 李四 +200
COMMIT; — ④ 提交，两步同时生效
SQL
q "SELECT * FROM account;"

# ③ 回滚演示：模拟第二步失败，整个事务回滚（余额不变）
"${MYSQL[@]}" -D "$DB" <<'SQL'
START TRANSACTION;
UPDATE account SET balance = balance – 300 WHERE id = 1;
— 假设此处应用检测到异常（如余额不足、账户冻结）
ROLLBACK; — 全部撤销，balance -300 不生效
SQL
q "SELECT * FROM account; — 余额应与上一步相同，证明 ROLLBACK 生效"

# ④ SAVEPOINT 部分回滚：只撤销保存点之后的操作，之前的操作仍可提交
"${MYSQL[@]}" -D "$DB" <<'SQL'
START TRANSACTION;
UPDATE account SET balance = balance – 100 WHERE id = 1;
SAVEPOINT sp1; — 设置保存点
UPDATE account SET balance = balance – 500 WHERE id = 1;
ROLLBACK TO SAVEPOINT sp1; — 只撤销 -500，保留 -100
COMMIT; — 最终只扣 100
SQL
q "SELECT * FROM account; — 张三应为 1000-200-100=700"

# ⑤ 隔离级别验证：REPEATABLE READ 下，同一事务内两次读到的数据一致
# 需要在两个终端做，这里用脚本模拟：终端 A 开启事务读两次，中间用另一个连接改数据
"${MYSQL[@]}" -D "$DB" -e "REPLACE INTO account (id,name,balance) VALUES (1,'张三',1000.00);" # 重置

# 会话 A（本连接）：BEGIN → 读一次 → 留出 10 秒窗口 → 再读一次，两次结果应相同（RR 特性）
# 请在这 10 秒内，另开一个终端执行：UPDATE account SET balance = balance + 100 WHERE id = 1; COMMIT;
echo ">>> 请在接下来 10 秒内，在另一个终端执行：UPDATE account SET balance=balance+100 WHERE id=1; COMMIT;"
"${MYSQL[@]}" -D "$DB" –init-command="SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ" <<'SQL'
START TRANSACTION;
SELECT balance AS RR_第一次读 FROM account WHERE id = 1;
SELECT SLEEP(10); — 留出窗口供另一个会话改数据并提交
SELECT balance AS RR_第二次读 FROM account WHERE id = 1; — 与第一次相同（可重复读）
COMMIT;
SQL

# ⑥ READ COMMITTED 对比：每次读都看到最新已提交数据（不可重复读）
"${MYSQL[@]}" -D "$DB" -t -e "
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance AS RC_第一次读 FROM account WHERE id = 1;
SELECT balance AS RC_第二次读 FROM account WHERE id = 1;
COMMIT;"

# ⑦ 长事务排查：运维必查的活跃事务列表（超过 60 秒的要重点关注）
q "SELECT trx_id, trx_started, trx_wait_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS 已运行秒数,
LEFT(trx_query, 50) AS 当前SQL
FROM information_schema.INNODB_TRX
ORDER BY trx_started LIMIT 10;"

# ⑧ 清理
"${MYSQL[@]}" -D "$DB" -e "DROP TABLE IF EXISTS account;"
echo "演示账户表已清理"

示例 11-2：锁等待复现、排查与死锁分析

# ══════════════════════════════════════════════════════════════════════
# 示例 11-2 锁：锁等待复现 + sys.innodb_lock_waits 排查 + 死锁分析
# 场景：线上出现「SQL 执行卡住不动」，需要定位是哪个事务持锁、该杀谁
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 确认锁等待超时时间与死锁检测开关
q "SELECT @@innodb_lock_wait_timeout AS 锁等待超时秒数,
@@innodb_deadlock_detect AS 死锁检测,
@@innodb_autoinc_lock_mode AS 自增锁模式;"

# ② 复现锁等待：会话 A 持锁不提交（后台运行，模拟跑事务的应用）
"${MYSQL[@]}" -D "$DB" <<'SQL'
START TRANSACTION;
UPDATE orders SET amount = amount + 1 WHERE order_id = (SELECT MIN(order_id) FROM orders);
SELECT CONNECTION_ID() AS 持锁会话ID, '事务未提交，锁保持中' AS 状态;
SQL

# ③ 复现锁等待：会话 B 改同一行 —— 会被阻塞直到超时（ERROR 1205）
# 用 timeout 命令限时 6 秒，避免脚本卡死
timeout 6 "${MYSQL[@]}" -D "$DB" -e "
START TRANSACTION;
UPDATE orders SET amount = amount + 1 WHERE order_id = (SELECT MIN(order_id) FROM orders);
COMMIT;" 2>&1 | grep -E 'ERROR|1205|Lock wait' && echo "↑ 已复现锁等待超时（ERROR 1205）" \\
|| echo "（持有锁的事务已结束，未复现；请开两个终端手动执行 ② 和 ③）"

# ④ 8.0+ 实时锁等待排查（推荐）：sys.innodb_lock_waits 一眼看清谁等谁
q "SELECT waiting_pid AS 等待方PID, waiting_query AS 等待方SQL,
blocking_pid AS 阻塞方PID, LEFT(blocking_query, 40) AS 阻塞方SQL,
wait_age_secs AS 已等待秒数
FROM sys.innodb_lock_waits\\G"

# ⑤ 8.0+ 锁明细（替代 8.4 已删除的 information_schema.INNODB_LOCKS）
q "SELECT lock_type, lock_mode, lock_status, LEFT(lock_data, 30) AS lock_data
FROM performance_schema.data_locks LIMIT 10;"

# ⑥ 锁等待关系（替代 8.4 已删除的 INNODB_LOCK_WAITS）
q "SELECT requesting_engine_lock_id, blocking_engine_lock_id
FROM performance_schema.data_lock_waits LIMIT 5;"

# ⑦ 找到阻塞源后：用 KILL 终止会话（生产环境务必先确认业务影响）
BLOCKER_PID=$("${MYSQL[@]}" -D "$DB" -N -B -e "
SELECT blocking_pid FROM sys.innodb_lock_waits LIMIT 1;" 2>/dev/null)
if [[ -n "$BLOCKER_PID" ]]; then
echo "检测到阻塞源 PID=$BLOCKER_PID"
# 先查该会话在干什么，再决定是否 KILL
q "SELECT id, user, host, db, command, time, LEFT(info, 60) AS sql_text
FROM information_schema.PROCESSLIST WHERE id = ${BLOCKER_PID};"
# "${MYSQL[@]}" -e "KILL ${BLOCKER_PID};" # 确认无误后取消注释执行
echo "KILL 命令已备好（注释状态）：先确认业务影响再执行"
else
echo "当前无锁等待"
fi

# ⑧ 死锁排查：SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段
"${MYSQL[@]}" -e "SHOW ENGINE INNODB STATUS\\G" | awk '/LATEST DETECTED DEADLOCK/,/TRANSACTIONS/' | head -40

# ⑨ 乐观并发控制（生产推荐）：用原子 UPDATE 代替 SELECT … FOR UPDATE，避免显式加锁
q "SELECT 'UPDATE stock SET n = n – 1 WHERE id = 1 AND n >= 1; — 受影响行数=1 才算扣减成功' AS 防超卖推荐写法;"

# ⑩ 8.0+ 队列场景神器：SKIP LOCKED 跳过被锁行，多消费者互不阻塞
q "SELECT 'SELECT * FROM task WHERE status = 0 ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;' AS 并发取任务写法;"

第 12 章 索引优化与执行计划

讲义无此章节，依据 MySQL 8.0 / 8.4 官方标准实践补充。

12.1 语法说明与适用场景

（1）索引类型与创建语法

— 普通 / 唯一 / 复合 / 前缀 / 全文 / 空间
CREATE INDEX idx_name ON tbl (col);
CREATE UNIQUE INDEX uk_name ON tbl (col);
CREATE INDEX idx_multi ON tbl (col1, col2, col3); — 复合索引，遵循最左前缀
CREATE INDEX idx_prefix ON tbl (varchar_col(16)); — 前缀索引，节省空间
CREATE FULLTEXT INDEX ft_name ON tbl (col) WITH PARSER ngram; — 全文索引
ALTER TABLE tbl ADD INDEX idx (col);
ALTER TABLE tbl DROP INDEX idx;

— 8.0+ 不可见索引（灰度验证索引是否有用，不用真删）
ALTER TABLE tbl ALTER INDEX idx_name INVISIBLE; — 对优化器隐藏
ALTER TABLE tbl ALTER INDEX idx_name VISIBLE; — 恢复可见

— 8.0.13+ 函数索引（表达式索引）
CREATE INDEX idx_func ON tbl ((LOWER(col)));
CREATE INDEX idx_month ON orders ((DATE_FORMAT(created_at, '%Y-%m')));

— 8.0+ 降序索引
CREATE INDEX idx_desc ON orders (created_at DESC);

— 生成列 + 索引（JSON 场景的标准解法，见第 13 章）
ALTER TABLE tbl ADD COLUMN gen VARCHAR(32) GENERATED ALWAYS AS (expr) STORED, ADD INDEX (gen);

（2）EXPLAIN 关键列解读（运维必会）

Extra 关键值：

（3）索引失效的六大场景（面试与实战高频）

补充：!=、<>、NOT IN 通常可以用索引（优化器会按代价选择），但在结果集占比高时会退化为全表扫描——不是"绝对失效"，而是"代价不划算"。

（4）8.0 索引新特性

（5）适用场景与方法论

12.2 可执行 Shell 示例

示例 12-1：慢查询定位与 EXPLAIN 分析全流程

# ══════════════════════════════════════════════════════════════════════
# 示例 12-1 性能排查：慢日志开启 → 聚合分析 → EXPLAIN 定位 → 加索引验证
# 场景：业务反馈「订单查询慢」，走完整排查闭环
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ───────────────────────────────────────══════════─────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 开启慢查询日志（会话级动态调整，8.0 支持；重启失效，持久化需写 my.cnf）
q "SELECT @@slow_query_log AS 慢日志开关, @@long_query_time AS 慢查询阈值秒,
@@slow_query_log_file AS 慢日志文件, @@log_queries_not_using_indexes AS 记录未用索引;"
"${MYSQL[@]}" -e "SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = ON;"
echo "慢日志已开启（阈值 1 秒）"

# ② 制造一条慢查询：无索引的大范围扫描
"${MYSQL[@]}" -D "$DB" -e "SELECT COUNT(*) FROM orders WHERE amount BETWEEN 100 AND 800;"

# ③ 用 mysqldumpslow 聚合慢日志（MySQL 自带，无需额外安装）
SLOW_LOG=$("${MYSQL[@]}" -N -B -e "SELECT @@slow_query_log_file;")
if [[ -r "$SLOW_LOG" ]]; then
echo "— 慢日志 Top5（按平均耗时排序）—"
mysqldumpslow -s at -t 5 "$SLOW_LOG" 2>/dev/null || echo "（mysqldumpslow 不可用，可直接查看 $SLOW_LOG）"
else
echo "慢日志文件不可读：$SLOW_LOG（需 root 权限或调整路径）"
fi

# ④ EXPLAIN 分析：看 type / key / rows / Extra 四列
q "EXPLAIN SELECT COUNT(*) FROM orders WHERE amount BETWEEN 100 AND 800\\G"

# ⑤ 加索引前先确认基数（Cardinality）：区分度高才值得建索引
q "SHOW INDEX FROM orders;"

# ⑥ 建索引（8.0 支持 INPLACE，不阻塞 DML；大表仍建议低峰执行）
"${MYSQL[@]}" -D "$DB" -e "CREATE INDEX idx_amount ON orders (amount);"
echo "索引 idx_amount 已创建"

# ⑦ 验证：type 应从 ALL 变为 range，rows 大幅下降，Extra 出现 Using index（覆盖索引）
q "EXPLAIN SELECT COUNT(*) FROM orders WHERE amount BETWEEN 100 AND 800\\G"

# ⑧ 用 EXPLAIN ANALYZE 看真实耗时（8.0.18+，包含每个节点的 actual time）
q "EXPLAIN ANALYZE SELECT COUNT(*) FROM orders WHERE amount BETWEEN 100 AND 800\\G"

# ⑨ JSON 格式：信息最全，含 cost 估算（8.0+）
q "EXPLAIN FORMAT=JSON SELECT COUNT(*) FROM orders WHERE amount BETWEEN 100 AND 800\\G"

# ⑩ 索引失效复现：对索引列做函数运算，索引立刻失效
q "EXPLAIN SELECT COUNT(*) FROM orders WHERE amount + 0 BETWEEN 100 AND 800\\G"
q "SELECT '↑ 对索引列做函数运算导致 type=ALL（全表扫描），应改成 amount BETWEEN 100 AND 800' AS 结论;"

# ⑪ 8.0+ 不可见索引：灰度验证「这个索引到底有没有用」
"${MYSQL[@]}" -D "$DB" -e "ALTER TABLE orders ALTER INDEX idx_amount INVISIBLE;"
q "EXPLAIN SELECT COUNT(*) FROM orders WHERE amount BETWEEN 100 AND 800\\G" # 应退回全表扫描
"${MYSQL[@]}" -D "$DB" -e "ALTER TABLE orders ALTER INDEX idx_amount VISIBLE;"
q "SELECT '确认索引有用后再保留；确认无用则用 DROP INDEX 删除，减少写放大' AS 灰度方法;"

# ⑫ 冗余索引与未使用索引排查（sys 库视图，8.0+）
q "SELECT * FROM sys.schema_redundant_indexes LIMIT 10;"
q "SELECT object_name, index_name FROM sys.schema_unused_indexes LIMIT 10;"

示例 12-2：索引失效六大场景复现 + 分批更新

# ══════════════════════════════════════════════════════════════════════
# 示例 12-2 索引失效复现 + 大表分批更新的正确姿势
# 场景：新同学写的 SQL 全表扫描，逐条对照六大失效场景定位原因
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 准备：建一个带复合索引的演示表
"${MYSQL[@]}" -D "$DB" <<'SQL'
DROP TABLE IF EXISTS idx_demo;
CREATE TABLE idx_demo (
id INT PRIMARY KEY AUTO_INCREMENT,
code VARCHAR(32) NOT NULL, — 字符串列，用于演示隐式转换
num INT NOT NULL,
created DATETIME NOT NULL,
INDEX idx_code_num (code, num), — 复合索引，最左前缀 = code
INDEX idx_num (num),
INDEX idx_created (created)
) ENGINE=InnoDB;

SET SESSION cte_max_recursion_depth = 10000;
INSERT INTO idx_demo (code, num, created)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 3000)
SELECT CONCAT('C', LPAD(n, 6, '0')), n, NOW() – INTERVAL n HOUR FROM s;
SQL
"${MYSQL[@]}" -D "$DB" -e "ANALYZE TABLE idx_demo;" # 刷新统计信息，保证 EXPLAIN 估算准确
echo "演示表 idx_demo 就绪（3000 行）"

# ② 场景 1：对索引列做函数运算 → 失效
q "EXPLAIN SELECT * FROM idx_demo WHERE DATE(created) = CURDATE()\\G"
q "SELECT '修复：WHERE created >= CURDATE() AND created < CURDATE() + INTERVAL 1 DAY' AS 方案;"

# ③ 场景 2：隐式类型转换（字符串列 vs 数字）→ 失效
q "EXPLAIN SELECT * FROM idx_demo WHERE code = 123\\G" # 失效：数字被转成字符串比较
q "EXPLAIN SELECT * FROM idx_demo WHERE code = '000123'\\G" # 生效：类型一致

# ④ 场景 3：前导通配符 → 失效
q "EXPLAIN SELECT * FROM idx_demo WHERE code LIKE '%123'\\G" # 失效：前导 %
q "EXPLAIN SELECT * FROM idx_demo WHERE code LIKE 'C0001%'\\G" # 生效：前缀匹配

# ⑤ 场景 4：违反最左前缀 → 复合索引失效
q "EXPLAIN SELECT * FROM idx_demo WHERE num = 100\\G" # idx_code_num 失效，改用 idx_num
q "EXPLAIN SELECT * FROM idx_demo WHERE code = '000123'\\G" # 生效：命中复合索引最左列

# ⑥ 场景 5：OR 连接非索引列 → 部分失效
q "EXPLAIN SELECT * FROM idx_demo WHERE num = 100 OR id = 200\\G"

# ⑦ 覆盖索引演示：只查索引列时 Extra 出现 Using index，不回表
q "EXPLAIN SELECT code, num FROM idx_demo WHERE code = '000123'\\G"
q "EXPLAIN SELECT * FROM idx_demo WHERE code = '000123'\\G" # SELECT * 需要回表

# ⑧ 消除 filesort：ORDER BY 字段进索引后，Extra 里的 Using filesort 消失
q "EXPLAIN SELECT * FROM idx_demo ORDER BY created LIMIT 10\\G"

# ⑨ 大表分批更新（生产必备）：用主键范围切分，每批独立事务，避免长事务与锁表
"${MYSQL[@]}" -D "$DB" <<'SQL'
— 用存储过程封装分批更新：每批 500 行，批间自动提交
DROP PROCEDURE IF EXISTS sp_batch_update;
DELIMITER $$
CREATE PROCEDURE sp_batch_update(IN p_batch INT)
BEGIN
DECLARE v_rows INT DEFAULT -1;
WHILE v_rows <> 0 DO
UPDATE idx_demo SET num = num + 0
WHERE num > 2500 LIMIT p_batch; — LIMIT 限制单批影响行数
SET v_rows = ROW_COUNT(); — 本批影响行数，0 表示处理完
— 每批结束隐式提交（autocommit=1），不持长锁
END WHILE;
END$$
DELIMITER ;
SQL
"${MYSQL[@]}" -D "$DB" -e "CALL sp_batch_update(500);"
echo "分批更新完成"

# ⑩ 直方图：数据倾斜时帮优化器做出正确选择（8.0+）
"${MYSQL[@]}" -D "$DB" -e "ANALYZE TABLE idx_demo UPDATE HISTOGRAM ON num WITH 16 BUCKETS;"
q "SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, HISTOGRAM
FROM information_schema.COLUMN_STATISTICS LIMIT 5;"

# ⑪ 清理
"${MYSQL[@]}" -D "$DB" -e "DROP PROCEDURE IF EXISTS sp_batch_update; DROP TABLE IF EXISTS idx_demo;"
echo "演示对象已清理"

第 13 章 JSON 与全文检索

讲义无此章节，依据 MySQL 8.0 / 8.4 官方标准实践补充。

13.1 语法说明与适用场景

（1）JSON 数据类型与函数

路径语法：$.key、$.a.b、$[0]、$.arr[*]、$**.key（递归通配）。

（2）⚠ JSON 索引的正确做法

JSON 列不能直接建索引。标准解法是生成列 + 索引：

— 推荐：生成列（Generated Column）+ 普通索引，兼容性最好
ALTER TABLE product
ADD COLUMN cpu VARCHAR(32) GENERATED ALWAYS AS (attrs–>>'$.cpu') STORED,
ADD INDEX idx_cpu (cpu);

— 备选：8.0.13+ 函数索引（表达式索引）
CREATE INDEX idx_cpu_func ON product ((CAST(attrs–>>'$.cpu' AS CHAR(32))));

（3）全文检索

— ① 建全文索引（英文默认解析器；中文必须指定 ngram）
CREATE FULLTEXT INDEX ft_name ON tbl (col1, col2); — 英文/默认
CREATE FULLTEXT INDEX ft_name ON tbl (col1, col2) WITH PARSER ngram; — 中文

— ② 三种检索模式
MATCH(col1, col2) AGAINST ('关键词' IN NATURAL LANGUAGE MODE) — 自然语言（默认），按相关性排序
MATCH(col1, col2) AGAINST ('+必须 -排除 前缀*' IN BOOLEAN MODE) — 布尔模式，支持运算符
MATCH(col1, col2) AGAINST ('关键词' WITH QUERY EXPANSION) — 查询扩展（联想扩展，慎用）

— ③ 布尔模式运算符
+ 必须包含 – 必须不包含 * 前缀通配 "短语" 精确短语
~ 降低相关性 < > 调整权重 () 分组

中文检索的关键配置：

# /etc/my.cnf —— 改 ngram_token_size 必须重启 mysqld
[mysqld]
ngram_token_size = 2
innodb_ft_min_token_size = 1 # 若要支持单字检索（英文场景）

（4）⚠ MyISAM vs InnoDB 全文检索差异（常见误区）

纠正一个流传很广的错误说法：「MySQL 全文检索会自动忽略出现频率超过 50% 的词」——这只对 MyISAM 成立，InnoDB 全文检索没有 50% 阈值。8.x 默认引擎是 InnoDB，这条规则已不适用。

（5）适用场景对比

13.2 可执行 Shell 示例

示例 13-1：JSON 数据存储、查询与索引优化

# ══════════════════════════════════════════════════════════════════════
# 示例 13-1 JSON：商品属性存储 + 查询 + 生成列索引优化
# 场景：商品规格属性不固定（不同品类字段不同），用 JSON 存，但要能按 CPU 型号筛选
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ──────────────────────────────────────────────────────────
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 建表：JSON 列 + 生成列 + 索引（生成列是 JSON 能被索引的唯一标准解法）
"${MYSQL[@]}" -D "$DB" <<'SQL'
DROP TABLE IF EXISTS product;
CREATE TABLE product (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
attrs JSON NULL, — ① 灵活属性列
cpu VARCHAR(32) GENERATED ALWAYS AS (attrs->>'$.cpu') STORED, — ② 生成列：把 JSON 里的 cpu 提成关系列
price DECIMAL(10,2) GENERATED ALWAYS AS (attrs->>'$.price') STORED,
INDEX idx_cpu (cpu), — ③ 在生成列上建索引，可正常走索引
INDEX idx_price (price)
) ENGINE=InnoDB;
SQL
echo "商品表 product 已建（含生成列索引）"

# ② 插入 JSON 数据：JSON_OBJECT 构造，注意插入时生成的列不能显式赋值
"${MYSQL[@]}" -D "$DB" <<'SQL'
INSERT INTO product (name, attrs) VALUES
('笔记本A', JSON_OBJECT('cpu','i7-13700H','mem',16,'price',6999)),
('笔记本B', JSON_OBJECT('cpu','i5-13500H','mem',16,'price',4999)),
('台式机C', JSON_OBJECT('cpu','i7-13700K','mem',32,'price',8999)),
('平板D', JSON_OBJECT('cpu','M2','mem',8,'price',3999));
SQL

# ③ 提取：-> 返回带引号的 JSON；->> 返回去引号的字符串（业务上通常用 ->>）
q "SELECT name,
attrs->'$.cpu' AS 用箭头_带引号,
attrs->>'$.cpu' AS 用双箭头_去引号,
JSON_EXTRACT(attrs, '$.mem') AS 等价的JSON_EXTRACT
FROM product;"

# ④ 查询：JSON_CONTAINS 判断是否包含某个键值
q "SELECT name, attrs->>'$.cpu' AS cpu FROM product
WHERE JSON_CONTAINS(attrs, '\\"i7-13700H\\"', '$.cpu');"

# ⑤ 路径查询：JSON_CONTAINS_PATH 判断字段是否存在（区分 one / all）
q "SELECT name,
JSON_CONTAINS_PATH(attrs, 'one', '$.mem', '$.gpu') AS 存在任一,
JSON_CONTAINS_PATH(attrs, 'all', '$.mem', '$.gpu') AS 全部存在
FROM product;"

# ⑥ 修改：JSON_SET（有则改无则增）/ JSON_REPLACE（只改不增）/ JSON_REMOVE（删除）
"${MYSQL[@]}" -D "$DB" -e "UPDATE product SET attrs = JSON_SET(attrs, '$.gpu', 'RTX4060') WHERE id = 3;"
q "SELECT name, JSON_PRETTY(attrs) AS 属性 FROM product WHERE id = 3\\G"

# ⑦ 索引验证：走生成列索引，type 应为 ref
q "EXPLAIN SELECT name, cpu FROM product WHERE cpu = 'i7-13700H'\\G"

# ⑧ 对比：直接在 JSON 列上查询 → 全表扫描（这就是必须加生成列的原因）
q "EXPLAIN SELECT name FROM product WHERE attrs->>'$.cpu' = 'i7-13700H'\\G"

# ⑨ 8.0+ JSON_TABLE：把 JSON 数组展开成关系行（报表、ETL 神器）
q "SELECT jt.*
FROM product p,
JSON_TABLE(p.attrs, '\\$' COLUMNS (
cpu VARCHAR(32) PATH '\\$.cpu',
mem INT PATH '\\$.mem',
price INT PATH '\\$.price'
)) AS jt
ORDER BY price DESC;"

# ⑩ 校验与长度：JSON_VALID 判断合法性（导入外部数据必做）
q "SELECT JSON_VALID('{\\"a\\":1}') AS 合法, JSON_VALID('{a:1}') AS 非法,
JSON_LENGTH(attrs) AS 顶层键值数 FROM product LIMIT 1;"

示例 13-2：中文全文检索（ngram 解析器）

# ══════════════════════════════════════════════════════════════════════
# 示例 13-2 全文检索：中文 ngram 索引 + 三种检索模式 + 相关性排序
# 场景：给知识库文章做站内搜索，要求支持中文关键词并按相关性排序
# ══════════════════════════════════════════════════════════════════════

# ── 连接前置 ─────────────────────────────────══──════════════════════─
if [[ -r "$HOME/.my.cnf" ]]; then
MYSQL=(mysql –defaults-extra-file="$HOME/.my.cnf")
else
MYSQL=(mysql -u root -p)
fi
DB='demo'
q() { "${MYSQL[@]}" -D "$DB" -t -e "$1"; }

# ① 确认 ngram 分词参数（注意：ngram_token_size 是只读的，改它必须写 my.cnf 并重启）
q "SHOW VARIABLES LIKE 'ngram_token_size';"
q "SHOW VARIABLES LIKE 'innodb_ft_min_token_size';"
q "SELECT '若需修改 ngram_token_size：在 /etc/my.cnf 的 [mysqld] 段加 ngram_token_size=2 后重启 mysqld' AS 说明;"

# ② 建文章表（中文全文索引必须显式指定 WITH PARSER ngram）
"${MYSQL[@]}" -D "$DB" <<'SQL'
DROP TABLE IF EXISTS article;
CREATE TABLE article (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(128) NOT NULL,
body TEXT NOT NULL,
FULLTEXT KEY ft_title_body (title, body) WITH PARSER ngram — ngram 解析器：中文必需
) ENGINE=InnoDB;
SQL
echo "文章表 article 已建（ngram 全文索引）"

# ③ 灌入中文测试数据
"${MYSQL[@]}" -D "$DB" <<'SQL'
INSERT INTO article (title, body) VALUES
('MySQL 索引优化实战', '本文讲解 B+Tree 索引原理、覆盖索引与最左前缀原则，以及如何使用 EXPLAIN 分析执行计划。'),
('MySQL 事务与锁机制', '深入解析 InnoDB 的事务隔离级别、行锁、间隙锁与临键锁，以及如何排查死锁问题。'),
('Linux 系统性能排查', '介绍 top、iostat、vmstat 等工具的使用，以及磁盘 IO 瓶颈的定位方法。'),
('MySQL 备份恢复方案', '对比 mysqldump、XtraBackup 与 binlog 增量恢复三种方案的适用场景与恢复步骤。'),
('数据库索引设计指南', '索引是数据库性能优化的核心，本文总结索引设计的十条经验与常见误区。');
SQL

# ④ 自然语言模式：默认模式，按相关性得分自动排序
# ⚠ 注意：MATCH() 里的列必须与建索引时的列完全一致（顺序、数量），否则报 ERROR 1191
q "SELECT id, title,
ROUND(MATCH(title, body) AGAINST('索引 优化' IN NATURAL LANGUAGE MODE), 4) AS 相关度
FROM article
WHERE MATCH(title, body) AGAINST('索引 优化' IN NATURAL LANGUAGE MODE)
ORDER BY 相关度 DESC;"

# ⑤ 布尔模式：支持 + 必须包含 / – 必须排除 / * 前缀通配 / "" 精确短语
q "SELECT id, title,
ROUND(MATCH(title, body) AGAINST('+MySQL -Linux' IN BOOLEAN MODE), 4) AS 相关度
FROM article
WHERE MATCH(title, body) AGAINST('+MySQL -Linux' IN BOOLEAN MODE)
ORDER BY 相关度 DESC;"

# ⑥ 前缀通配（布尔模式）：'优化*' 会匹配「优化」「优化器」「优化方案」
q "SELECT id, title FROM article
WHERE MATCH(title, body) AGAINST('优化*' IN BOOLEAN MODE);"

# ⑦ 精确短语：双引号包裹，要求词序完全一致
q "SELECT id, title FROM article
WHERE MATCH(title, body) AGAINST('\\"性能优化\\"' IN BOOLEAN MODE);"

# ⑧ 常见错误复现：MATCH 列与索引列不一致 → ERROR 1191 Can't find FULLTEXT index
"${MYSQL[@]}" -D "$DB" -e "SELECT id FROM article WHERE MATCH(title) AGAINST('索引');" 2>&1 \\
| grep -E 'ERROR|1191' && echo "↑ 已复现：MATCH() 的列必须与 FULLTEXT 索引列完全一致" || echo "（未复现）"

# ⑨ 索引重建：全文索引在数据大量变更后需要 OPTIMIZE TABLE 重建（InnoDB 用 OPTIMIZE，不是 REPAIR）
"${MYSQL[@]}" -D "$DB" -e "OPTIMIZE TABLE article;"
q "SELECT 'InnoDB 全文索引用 OPTIMIZE TABLE 重建；MyISAM 才用 REPAIR TABLE' AS 注意;"

# ⑩ 性能对比：全文检索 vs LIKE '%关键词%'（后者必然全表扫描）
q "EXPLAIN SELECT id FROM article WHERE MATCH(title, body) AGAINST('索引' IN BOOLEAN MODE)\\G"
q "EXPLAIN SELECT id FROM article WHERE body LIKE '%索引%'\\G"
q "SELECT '全文索引 EXPLAIN 的 key 列应显示 ft_title_body；LIKE 则为 NULL 且 type=ALL' AS 结论;"

# ⑪ 搜索日志：把用户搜索词记录下来，用于后续优化分词与热门词分析
"${MYSQL[@]}" -D "$DB" <<'SQL'
CREATE TABLE IF NOT EXISTS search_log (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
keyword VARCHAR(128) NOT NULL,
hit_cnt INT DEFAULT 0,
searched_at DATETIME DEFAULT CURRENT_TIMESTAMP,
KEY idx_kw (keyword)
) ENGINE=InnoDB;
SQL
KW='索引 优化'
HITS=$("${MYSQL[@]}" -D "$DB" -N -B -e "
SELECT COUNT(*) FROM article WHERE MATCH(title, body) AGAINST('${KW}' IN BOOLEAN MODE);")
"${MYSQL[@]}" -D "$DB" -e "INSERT INTO search_log (keyword, hit_cnt) VALUES ('${KW}', ${HITS});"
q "SELECT keyword, hit_cnt, searched_at FROM search_log ORDER BY id DESC LIMIT 5;"

第 14 章 系统性验证与总结报告

14.1 各知识点逐条验证

验证方法说明

• Shell 正确性：文档中所有 bash 代码块已用 bash -n（语法检查）+ bash -u -n（未绑定变量检查）逐块静态校验，结果见 validation_report.md。
• SQL 正确性：本机为 Windows 环境（无 mysql 客户端），SQL 部分按 MySQL 8.0 / 8.4 官方语法逐条人工核验，重点核对函数名、参数顺序、版本特性门槛与 8.4 破坏性变更。
• 场景合理性：每个示例均对应一个真实运维诉求，并在代码注释中标注场景。

14.2 已验证修正内容清单

以下为本次编写过程中明确核对并修正的内容，逐条列出"原问题 → 修正依据"：

14.3 核心内容总结性汇报

（1）知识地图：按"使用频率 × 生产价值"分类

第一梯队 —— 每天都在用（必须滚瓜烂熟）

第二梯队 —— 大幅提升效率的 8.0 新特性（5.7 用户升级的核心收益）

第三梯队 —— 架构与工程化能力

（2）生产落地的六条行动建议

（3）与讲义的对应关系

附录 A 常见错误码速查

附录 B 配套脚本使用指南

mysql-advanced-init.sh（演示库一键初始化）

使用示例：

# ① 首次初始化（推荐）
bash mysql-advanced-init.sh –all

# ② 只想看会做什么，不实际改数据（Windows/Git Bash 离线自测用）
DRY_RUN=1 bash mysql-advanced-init.sh –all

# ③ 重复执行（幂等，不会重复灌数据）
bash mysql-advanced-init.sh –init

# ④ 验收
bash mysql-advanced-init.sh –verify

# ⑤ 推倒重来
bash mysql-advanced-init.sh –reset –force

前置条件：

文档版本：v1.0
编写日期：2026-09-01
依据讲义：05.MySQL高级SQL语句.pdf（第 5 章，44 页）
适用版本：MySQL 8.0+ / 推荐 8.4 LTS
校验状态：Shell 代码块 bash -n 全部通过；SQL 按 8.0/8.4 官方语法人工核验；实机执行输出待在 Linux 服务器运行后回填 validation_report.md