关键词：MySQL 8.0、CTE、递归查询、JSON_TABLE、窗口函数、不可见索引、密码策略

大家好！我是数据库小学妹。

MySQL 8.0 是MySQL的一次重大升级，在‌性能、安全性、开发效率和可管理性‌等方面引入了多项关键特性。8.0这几个功能，用好了能让你写代码省一半功夫。今天小学妹不带大家追新，就聊聊8.0里那些真正好用的实操技能。

---

一、都在说8.0，到底值不值得升？

如果你现在用的5.7没毛病，升级可以慢慢来。但如果你经常写复杂SQL，这几个功能你一定要知道。

我列了个对比表，看完你就有数了：

5.7写起来啥样	8.0怎么搞定
递归查询要写3层自连接，晕头转向	CTE递归，一行搞定
取个JSON数据要写七八行JSON_EXTRACT	JSON_TABLE，像查表一样简单
想删除无用索引又不敢，纠结半天	不可见索引，先藏起来试试
窗口函数用起来卡卡的	8.0优化后流畅多了

---

二、CTE：子查询的"救星"

啥是CTE？你就理解成给一段查询结果起个名字，后面想用直接写名字就行。

就像Excel里给某个区域起名，后面公式里直接用那个名，不用每次都重写那片区域。

普通CTE：告别嵌套嵌套嵌套

5.7那样写（看瞎眼版）：

SELECT * FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg
WHERE avg_salary > 5000;

8.0这样写（清爽版）：

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT * FROM dept_avg WHERE avg_salary > 5000;

就多了一个WITH，结构一下子清晰了有没有。

递归CTE：组织架构树的克星

这个真的太常用了！比如查某个部门下属的所有子部门：

WITH RECURSIVE dept_tree AS (
    SELECT id, department_name, parent_id, 1 AS level
    FROM departments WHERE id = 1

    UNION ALL

    SELECT d.id, d.department_name, d.parent_id, dt.level + 1
    FROM departments d
    INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree ORDER BY level, id;

递归的逻辑：

1. 先查出"起点"（比如技术部，level=1）
2. 然后自己调用自己，找下属部门（level=2、3……）
3. 找不着更多了就停止

以前实现这个要写存储过程或者复杂的自连接，现在一行WITH RECURSIVE搞定。做权限树、商品分类、层级结构的同学，这个技能必须有！

---

三、JSON_TABLE：把JSON当表查

接口返回的数据是JSON， 取个数太费劲了是不是？

5.7的取法（累觉不爱）：

SELECT
    JSON_EXTRACT(user_info, '$.name') AS name,
    JSON_EXTRACT(user_info, '$.age') AS age,
    JSON_EXTRACT(user_info, '$.address.city') AS city
FROM users;

8.0的取法（清爽）：

SELECT *
FROM JSON_TABLE(
    '[{"name":"xiaok","age":25,"addr":{"city":"Beijing"}},
      {"name":"lisi","age":30,"addr":{"city":"Shanghai"}}]',
    '$[*]' COLUMNS (
        name VARCHAR(50) PATH '$.name',
        age INT PATH '$.age',
        city VARCHAR(50) PATH '$.addr.city'
    )
) AS user_data;

结果直接就是一张表：

name	age	city
xiaok	25	Beijing
lisi	30	Shanghai

这就是JSON_TABLE的魔法：把JSON变成虚拟表，然后用SQL随便蹂躏。

实战：解析订单里的商品列表

SELECT
    order_id,
    product_name,
    quantity,
    price
FROM orders, JSON_TABLE(
    order_items,
    '$[*]' COLUMNS (
        product_name VARCHAR(100) PATH '$.productName',
        quantity INT PATH '$.qty',
        price DECIMAL(10,2) PATH '$.productPrice'
    )
) AS items;

一条SQL解析完，代码量少70%！

---

四、窗口函数：数据分析神器

8.0的窗口函数比之前版本流畅多了，这几个用法一定要会：

1. 排名：ROW_NUMBER

班级成绩排名，只显示前10：

SELECT *
FROM (
    SELECT
        name,
        score,
        ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
    FROM exam_scores
) AS ranked
WHERE rank <= 10;

2. 移动平均：报表必备

过去7天销量移动平均：

SELECT
    sale_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales;

做销售报表的同学，这个太香了！

3. 取首尾：FIRST_VALUE / LAST_VALUE

每个部门的最高最低工资：

SELECT
    department,
    employee_name,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_salary,
    LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS lowest_salary
FROM employees;

---

五、不可见索引：删除索引的"后悔药"

想删掉一个索引，又怕删完某个慢查询爆炸？8.0给你买了份"后悔药"：

-- 先隐藏索引，观察一段时间
ALTER TABLE orders ALTER INDEX idx_order_date INVISIBLE;

-- 这段时间查询优化器会自动忽略这个索引
-- 你去观察业务有没有变慢

-- 确认没问题，真删除
ALTER TABLE orders DROP INDEX idx_order_date;

先藏起来试试水，没问题再动手，稳！

---

六、密码策略：企业级安全

8.0的密码管理强了不少：

功能	用来干啥
密码过期	强制多久改一次密码
双重密码	新旧密码同时生效，平滑切换
密码历史	不许用最近用过的N个密码
角色管理	批量给权限，不用一个个配

示例：设置90天密码过期

CREATE USER 'xiaok'@'%' IDENTIFIED BY 'Password123!'
PASSWORD EXPIRE INTERVAL 90 DAY;

---

七、其他小但有用的特性

降序索引

CREATE INDEX idx_salary_desc ON employees(salary DESC);

以前ORDER BY DESC要临时排，现在有索引直接用！

EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_date > '2025-01-01';

8.0会告诉你实际执行时间，不再只是预估。

CHECK约束生效了

CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 0 AND age <= 150),
    status VARCHAR(10) CHECK (status IN ('active', 'inactive'))
);

5.7里CHECK约束形同虚设，8.0来真的了！

---

八、升级前避坑

坑一：语法不兼容

• 8.0的新语法在5.7里跑不了
• 建议：先用测试环境跑一遍

坑二：密码插件变了

• 8.0默认用caching_sha2_password，部分老客户端不支持
• 建议：升级时保持mysql_native_password，或者升级客户端

坑三：JSON函数行为

• 某些JSON函数返回格式有变化
• 建议：检查项目里用到的JSON函数，做兼容性测试

---

九、总结

今天聊的这些功能，主要就是：

• 代码更短：CTE、JSON_TABLE省很多行
• 用起来更爽：窗口函数、不可见索引都是刚需
• 企业级功能更全：密码策略、角色管理

能升级就升级，升之前做好测试就行。

还在用5.7的同学，不妨先把今天这几个功能用起来，真的能省很多功夫！

我是数据库小学妹，一个用设计师思维学数据库的转行人。你现在用的MySQL的哪个版本？欢迎一起交流。

---

本文基于MySQL 8.0.30+版本整理。不同小版本可能有差异，建议以官方文档为准。