学完这篇，你能把 MySQL 8.0 的窗口函数、CTE 和 JSON 增强这三块真正用起来，写出 5.7 时代要绕一大圈子查询才能实现的 SQL。
第一步：先确认你的版本
新特性不是每个 8.0 小版本都有，先跑一句：
SELECT VERSION();
- 8.0.0 起：窗口函数、CTE、降序索引
- 8.0.14 起：哈希连接
- 8.0.17 起：JSON 多值索引、
CAST(... AS UNSIGNED ARRAY)
- 8.0.16 起：CHECK 约束真正生效
如果版本是 5.7，下面的语法会直接报错，先升级再说。
注意：升级到 8.0 后，默认认证插件变成 caching_sha2_password，老版本 Navicat、老 PHP 的 mysqli 可能连不上，报 Authentication plugin cannot be loaded。要么升级客户端，要么对该用户执行 ALTER USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY '密码';。
第二步：窗口函数——不折叠行的聚合
普通 GROUP BY 会把多行压成一行，窗口函数则是「每行都保留，同时看到分组统计」。
排名场景，取每个版块里发帖最多的前 3 个人：
SELECT * FROM (
SELECT
board_id, user_id, post_count,
ROW_NUMBER() OVER (PARTITION BY board_id ORDER BY post_count DESC) AS rn
FROM user_board_stat
) t
WHERE rn <= 3;
几个常用函数：
| 函数 | 作用 |
|---|
ROW_NUMBER() | 连续序号，1 2 3 4 |
RANK() | 并列跳号，1 2 2 4 |
DENSE_RANK() | 并列不跳号，1 2 2 3 |
LAG(x,1) / LEAD(x,1) | 取上一行／下一行 |
SUM() OVER (...) | 累计求和 |
累计增长曲线用窗口帧：
SELECT d, amount,
SUM(amount) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum
FROM daily_stat;
注意：窗口函数不能直接写在 WHERE 里，因为它在 WHERE 之后才计算。想按 rn <= 3 过滤，必须像上面那样套一层子查询（或用 CTE）。
第三步：CTE——把 SQL 拆成有名字的步骤
WITH 就是给子查询起名字，读起来像流水账：
WITH hot AS (
SELECT id, title FROM posts WHERE views > 1000
),
replied AS (
SELECT post_id, COUNT(*) c FROM replies GROUP BY post_id
)
SELECT h.title, IFNULL(r.c,0) AS reply_count
FROM hot h LEFT JOIN replied r ON r.post_id = h.id;
递归 CTE 是它的杀手锏，查版块树、评论盖楼都用它：
WITH RECURSIVE tree AS (
SELECT id, parent_id, name, 1 AS lv
FROM boards WHERE parent_id = 0
UNION ALL
SELECT b.id, b.parent_id, b.name, t.lv + 1
FROM boards b JOIN tree t ON b.parent_id = t.id
)
SELECT * FROM tree ORDER BY lv, id;
注意：递归 CTE 写错终止条件会无限循环，MySQL 默认上限 cte_max_recursion_depth = 1000，超了报 Recursive query aborted。真需要更深就 SET SESSION cte_max_recursion_depth = 100000;，但更该检查数据里有没有成环的 parent_id。
第四步：JSON 增强——能查、能索引、能改局部
8.0 里 JSON 不再是「只能存进去拿不出来」的字段。
（1）JSON_TABLE 把 JSON 数组摊平成行
SELECT jt.name, jt.price
FROM goods g,
JSON_TABLE(g.attrs, '$.spec[*]' COLUMNS (
name VARCHAR(32) PATH '$.name',
price DECIMAL(10,2) PATH '$.price'
)) AS jt;
配套的 JSON_ARRAYAGG() / JSON_OBJECTAGG() 则是反向聚合。
（2）多值索引，让 JSON_CONTAINS 走索引
ALTER TABLE goods
ADD INDEX idx_tags ((CAST(attrs->'$.tags' AS UNSIGNED ARRAY)));
SELECT * FROM goods WHERE JSON_CONTAINS(attrs->'$.tags', '3');
（3）局部更新不再重写整份文档
8.0 对 JSON_SET、JSON_REPLACE 支持原地部分更新，只要新值不比旧值大，就不会整段重写，大 JSON 更新明显变快。
注意：多值索引的表达式必须严格写成 CAST(列->'$.路径' AS 类型 ARRAY)，且 -> 是 JSON 类型、->> 才是文本，两者不能混用，写错会导致索引建不上或查询不走索引。
第五步：顺手用上的三个小特性
- 降序索引：
INDEX idx (created_at DESC)，以前 DESC 只是语法糖，8.0 才是真降序
- 函数索引：
INDEX ((LOWER(title)))，让大小写不敏感查询走索引
- CHECK 约束：
CHECK (price >= 0)，8.0.16 起真正校验，不再是摆设
小结
- 版本先查：窗口函数和 CTE 从 8.0.0 起，JSON 多值索引要 8.0.17+
- 窗口函数保留明细行做排名/累计，不能进
WHERE，需外套子查询
- CTE 用于拆分复杂查询，递归 CTE 记得防环、注意递归深度上限
- JSON 三件套：
JSON_TABLE 摊平、多值索引加速、原地更新省 IO
- 升级请一并处理
caching_sha2_password 的客户端兼容问题
正式会员
见习用户
认证极客
钻石卡会员
