一、为什么说 8.0 是「写法变了」

教材列了十几条：窗口函数、公共表表达式、降序索引、JSON 增强、角色管理、原子 DDL、 默认 utf8mb4、新认证插件…… 这一串得分两类看，混在一起记只会记成一张清单。

判断标准很简单：不改代码也能跑，说明只是「内部变了」。 原来写不出来的查询现在一句能写，才叫「写法变了」。 三样里最值钱的是窗口函数。 它把一整类「排名、累计、比上一行」的问题，从自连接里解放了出来。

二、窗口函数：保留每一行，再加一列

需求：每个部门内部按金额排名，取前三。 5.7 没有窗口函数，只能自连接：同一张表跟自己按部门连起来，再数「不比它小的有几条」。 8.0 一句 ROW_NUMBER() OVER (PARTITION BY dept ORDER BY amt DESC) 就完了。

三个函数的差别只在「遇到相同的值」那一刻，其余完全一样。 记住一句话就够：要「取三条」用 ROW_NUMBER，要「第几名」用 RANK。 用错了不会报错，只会悄悄少给或多给几行。 OVER 括号里那两段也得记住：PARTITION BY 是分组，ORDER BY 是组内排序。 它和 GROUP BY 完全不同：GROUP BY 把多行压成一行，窗口函数保留所有行，只是多挂一列。

三、CTE：把嵌套摊平，还能递归

CTE 就是 WITH 名字 AS (子查询)，给子查询起个名，后面当表用。 它的价值不在快，在于把「从里往外读」变成「从上往下读」。 更关键的是【递归 CTE】：只有它能表达「一直往下找」。 组织树、菜单树、评论盖楼、物料清单展开，全靠它。

import sqlite3
c = sqlite3.connect(":memory:")
c.execute("CREATE TABLE dept (id INTEGER PRIMARY KEY, name TEXT,"
          " parent INTEGER REFERENCES dept(id))")
c.executemany("INSERT INTO dept VALUES (?,?,?)", [
    (1, "总公司", None), (2, "研发中心", 1), (3, "市场中心", 1),
    (4, "后端组", 2), (5, "前端组", 2), (6, "华东区", 3), (7, "华南区", 3)])

SQL = """
WITH RECURSIVE sub(id, name, lv, path) AS (
    SELECT id, name, 0, CAST(id AS TEXT) FROM dept WHERE name = ?
    UNION ALL
    SELECT d.id, d.name, s.lv + 1, s.path || '/' || d.id
    FROM dept d JOIN sub s ON d.parent = s.id
)
SELECT lv, name, path FROM sub
"""
for root in ("研发中心", "总公司"):
    rows = c.execute(SQL, (root,)).fetchall()
    rows.sort(key=lambda r: [int(x) for x in r[2].split("/")])
    print("[%s] 一共 %d 个节点（含自己）" % (root, len(rows)))
    for lv, name, path in rows:
        print("  " + "  " * lv + name)

[研发中心] 一共 3 个节点（含自己）
  研发中心
    后端组
    前端组
[总公司] 一共 7 个节点（含自己）
  总公司
    研发中心
      后端组
      前端组
    市场中心
      华东区
      华南区

输出的两层缩进就是树的形状：以「研发中心」为根只有 3 个节点，以「总公司」为根有 7 个。 递归 CTE 的结构固定是三块，换任何场景都是这三块。

最容易踩的坑是忘了终止条件：只要数据成环，它就会一直长下去。 MySQL 默认最多递归 1000 层，超了报 Recursive query aborted。 这个默认值由 cte_max_recursion_depth 控制，别随手调到很大。

四、降序索引和其余几样

8.0 之前，CREATE INDEX ... (col DESC) 里的 DESC 是被悄悄忽略的，索引一律升序建。 8.0 真的按降序存，所以 ORDER BY ... DESC 可以顺着索引读，不用再排一遍。 其余几样，一句话一条：

最后一行是升级时最常踩的坑。 5.7 时代的老驱动连 8.0 会报认证失败，改法有两个：升级驱动， 或者把账号改回 mysql_native_password。 前者是正路，后者是缓兵之计。

五、动手 Demo：8.0 那三样：窗口函数、CTE、降序索引，各跑一遍

同一份数据、同一台沙箱，把「5.7 怎么写」和「8.0 怎么写」各跑一遍。 本沙箱的 SQLite 支持窗口函数和 CTE，所以这两样能真跑。 降序索引只能演效果，语法照 MySQL 原样贴出来。

import sqlite3
c = sqlite3.connect(":memory:")
c.execute("CREATE TABLE sale (id INTEGER PRIMARY KEY,"
          " dept TEXT, name TEXT, amt REAL)")
rows = [(i, "D%d" % (i % 3 + 1), "人%03d" % i,
         1000.0 + (i * 37) % 9000) for i in range(1, 301)]
c.executemany("INSERT INTO sale VALUES (?,?,?,?)", rows)

sql57 = ("SELECT a.dept, a.name, a.amt, COUNT(*) AS rk FROM sale a "
         "JOIN sale b ON a.dept = b.dept AND a.amt <= b.amt "
         "GROUP BY a.dept, a.name, a.amt HAVING COUNT(*) <= 3 "
         "ORDER BY a.dept, rk")
sql80 = ("SELECT dept, name, amt, ROW_NUMBER() OVER "
         "(PARTITION BY dept ORDER BY amt DESC) AS rk FROM sale")

print("5.7 自连接    %2d 行" % len(c.execute(sql57).fetchall()))
mid = c.execute("SELECT COUNT(*) FROM sale a JOIN sale b "
                "ON a.dept = b.dept AND a.amt <= b.amt").fetchone()[0]
print("中间结果      %2d 行" % mid)

c.execute("CREATE VIEW v_rank AS " + sql80)
q80 = ("SELECT dept, name, amt, rk FROM v_rank WHERE rk <= 3 "
       "ORDER BY dept, rk")
print("8.0 窗口函数  %2d 行" % len(c.execute(q80).fetchall()))

实跑输出：

--------------------------------------------------------------------
第五课：8.0 那几样新东西，到底解决了什么
--------------------------------------------------------------------

  **教材列了 8.0 的新特性一长串，真正改变写法的只有三样：窗口函数、CTE、降序索引。**
  **其余那些（默认 utf8mb4、JSON 增强、角色管理）是「更好用」，这三样是「写法变了」。**
  **这一课用同一份数据，把「5.7 怎么写」和「8.0 怎么写」各跑一遍。**
  **注意：本沙箱的 SQLite 3.53 支持窗口函数和 CTE，所以这两样能真跑。**
  **降序索引只能演效果，语法照 MySQL 贴。**

  一、窗口函数：排名不用自连接了
  --------------------------------------------------------------------
  **需求：每个部门内部按金额排名，取前三。5.7 时代只能这么写——**

  [教材原码 · 本沙箱不执行]
  | SELECT a.dept, a.name, a.amt, COUNT(*) AS rk
  | FROM sale a
  | JOIN sale b ON a.dept = b.dept AND a.amt <= b.amt
  | GROUP BY a.dept, a.name, a.amt
  | HAVING COUNT(*) <= 3;
    （自连接 + 分组计数，中间结果要把部门内两两配对全摆出来）

  **这一条在沙箱里跑通 9 行，耗时 0.0153 秒。**
  **8.0 用窗口函数，一句就够：**
  SELECT dept, name, amt, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY amt DESC) AS rk
  FROM sale
  **把它包成视图，外面再筛「rk 小于等于 3」——这样两边取的都是前三名。**

  mysql> SELECT dept, name, amt, rk FROM v_rank WHERE rk <= 3 ORDER BY dept, rk
  dept name  amt  rk
  ------------------
  D1   人243 9991 1
  D1   人240 9880 2
  D1   人237 9769 3
  D2   人241 9917 1
  D2   人238 9806 2
  D2   人235 9695 3
  D3   人242 9954 1
  D3   人239 9843 2
  D3   人236 9732 3
  9 rows in set

  写法          耗时       结果行数  中间结果
  -------------------------------------------------------
  5.7 自连接    0.0153 秒  9         15150 行
  8.0 窗口函数  0.0006 秒  9         不落中间表，一遍扫完
  **两条 SQL 的差距不只是字数。**
  **自连接那条的中间结果是「部门内的两两配对」，300 行输入吹成了 15150 行。**
  **窗口函数只扫一遍，排名是边扫边算出来的，不需要把两两配对先摆出来。**
  **这台机器上 5.7 那条明显更慢，慢就慢在这坨中间结果上。**
  **但这个差距不能外推——换机器、换缓存状态、换数据分布，倍数就变。**
  **真正稳的是中间结果的行数：它只跟数据量有关，跟机器无关。**

  二、CTE：把嵌套子查询摊平
  --------------------------------------------------------------------
  **需求：找出「销售额高于本部门平均」的人，再按超额比例排序。**
  **5.7 得套两层子查询：**

  [教材原码 · 本沙箱不执行]
  | SELECT name, amt FROM sale
  | WHERE amt > (SELECT AVG(amt) FROM sale s2 WHERE s2.dept = sale.dept)
  | ORDER BY amt DESC LIMIT 5;

  **8.0 可以先把部门均值算成一个「临时视图」，再拿它去 join：**
  WITH avg_dept AS (SELECT dept, AVG(amt) AS a FROM sale GROUP BY dept)
  SELECT s.name, s.amt, avg_dept.a AS dept_avg
  FROM sale s JOIN avg_dept ON avg_dept.dept = s.dept
  WHERE s.amt > avg_dept.a
  ORDER BY s.amt DESC
  LIMIT 3
  name  amt  dept_avg
  -------------------
  人243 9991 4895.5
  人242 9954 4858.5
  人241 9917 4821.5
  3 rows in set
  **CTE 的好处不是快，是好读：语句从内往外读变成了从上往下读。**
  **递归 CTE 更是只有它做得到——组织树、菜单树全靠它。**

  三、降序索引：省掉一次倒序
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | CREATE INDEX idx_amt_desc ON sale (amt DESC);
  | -- 8.0 之前 DESC 是被忽略的，索引一律升序建；
  | -- 8.0 真的按降序存，ORDER BY amt DESC 可以直接顺着索引读。

  **SQLite 也支持 DESC 索引，这里真建一个，看计划有没有变：**

  索引           ORDER BY amt DESC 的计划
  -----------------------------------------------------
  amt 升序       SCAN sale USING COVERING INDEX i_amt
  amt DESC 降序  SCAN sale USING COVERING INDEX i_amt_d
  **两行都用了索引，但只有第二行是「顺着读」，第一行要「倒着读」。**
  **小数据看不出差别，大表上倒序读会多一次临时排序。**

  四、其余那些，一句话说完
  --------------------------------------------------------------------

  特性                   解决了什么
  -------------------------------------------------------------
  默认字符集 utf8mb4     emoji 和生僻字终于能存了
  JSON 增强              可以按路径查、按路径建索引
  角色（ROLE）           权限可以打包发，不用一个个授
  原子 DDL               建表失败不会留下半张表
  caching_sha2_password  默认认证插件换了，老客户端连不上就是它
  **最后一行是升级时最常踩的坑：5.7 的老驱动连 8.0 会报认证失败。**
  **不是密码错了，是插件不认。**

第一段最值得看的不是耗时，是那个 15150。 300 行输入，自连接那条要先摆出 15150 行的中间结果，才数得出名次。 窗口函数只扫一遍，边扫边算，中间结果一行都不落。 耗时那一列两个数差了二十来倍，但这个倍数不能外推。 换机器、换缓存状态、换数据分布，数就变。 真正跟机器无关的是中间结果的行数。 第二段的 CTE 只用了六个字的好处：从上往下读。 注意它和子查询的结果完全一样，换来的只有可读性——可读性在三个月后改自己的 SQL 时才知道值钱。 第三段是真的建了两个索引对比：升序索引和 DESC 索引。 两行计划都显示用了索引，但只有降序那个是「顺着读」。 小数据看不出差别，大表上倒序读会多一次排序。 最后那张表里，只有 caching_sha2_password 会当场咬人。 它是 8.0 的默认认证插件，5.7 的老驱动不认，报的却是「认证失败」。 这种「报错说得不明不白」的坑，知道一次就够。

30 秒实验

1. 把 Demo 里的 ROW_NUMBER 换成 RANK，再跑一遍。这份数据没有并列金额，所以结果一模一样。 想看到差别，得把某两行的 amt 改成相同的值。
2. 把 CODE221 里递归 CTE 的 WHERE name = ? 删掉，看返回几行。 锚点一下子变成 7 行，每行都往下长，同一节点会被算好几次。 这就是「锚点必须只圈一个起点」的原因。
3. 把 Demo 的数据从 3 个部门改成 1 个部门，窗口函数会不会更快？ 会，因为不需要分组了——PARTITION BY 全落在同一组，等于只排一次序。

常见误区

1. 「窗口函数比 GROUP BY 快。」 不是一类东西，没法比。GROUP BY 把多行压成一行，窗口函数保留每一行再加一列。 快慢取决于具体查询，不取决于用哪个语法。
2. 「CTE 是临时表，会落盘。」 CTE 是语法结构，不是建了张表。 优化器可能把它合并进主查询，也可能物化成临时结果，要看执行计划才知道。
3. 「升到 8.0 就不用管字符集了。」 新库默认 utf8mb4 是对的，但 5.7 升上来的老库还是 utf8。 要改得一张张表 CONVERT TO CHARACTER SET，2.4 节会算这笔账。

下一篇讲机器怎么摆：单机、主从、集群，三种方案各要几台机器， 以及为什么「写」才是那道绕不过去的坎。

• ☐ （本篇完，下一篇 2.3 MySQL 的应用环境）

相关笔记

• 了解 MySQL
• MySQL 的应用环境
• 45 课 MySQL 必知必会：查询语法的系统梳理
• 🛢️ MySQL从入门到精通 · 索引