{}JSJSON

MySQL 8.x 高级查询实战：CTE、窗口函数与 JSON 函数完全指南

深入解析 MySQL 8.x 三大核心特性：CTE 公共表表达式、窗口函数和 JSON 函数，包含完整代码示例、性能对比和生产环境避坑指南。

数据库 2026-06-07 18 分钟

MySQL 8.0 引入了大量原本只在 PostgreSQL 和 Oracle 中才有的高级 SQL 特性，但根据 Stack Overflow 2025 年调查，超过 60% 的 MySQL 开发者仍在使用 MySQL 5.x 时代的写法——子查询嵌套、应用层聚合、把 JSON 当纯文本处理。如果你还在用 GROUP_CONCAT 模拟行转列，或者在应用层拼接 JSON，这篇文章会让你重新认识 MySQL 8.x 的查询能力。

本文聚焦三个最实用的高级特性：CTE（公共表表达式）、窗口函数和 JSON 函数，每个特性都有完整的可运行代码示例和性能对比。

📋 MySQL 8.x 关键特性速览

版本 关键特性 对本文的影响
8.0.1 CTE、窗口函数基础 核心语法支持
8.0.14 CTE MATERIALIZED 提示 性能优化手段
8.0.17 多值索引、JSON_TABLE JSON 查询质变
8.0.22 WINDOW 子句、FETCH 语法 SQL 可读性提升
8.0.28 JSON_SCHEMA_VALID JSON 校验能力
8.4 LTS 长期支持版本 生产环境首选

💡 **提示：**如果你的 MySQL 版本低于 8.0.17，窗口函数和 CTE 可以使用，但 JSON_TABLE 和多值索引不可用。生产环境建议至少升级到 8.0.36 或直接使用 8.4 LTS 版本。

🔍 一、CTE：告别子查询嵌套，写出可读的 SQL

公共表表达式（Common Table Expressions，CTE）是 MySQL 8.0 最重要的语法改进之一。它用 WITH 关键字定义一个临时命名结果集，可以在后续查询中多次引用，彻底解决了子查询嵌套可读性差的问题。

📌 基础语法与递归查询

CTE 的基本语法非常直观。非递归 CTE 本质上是给子查询起个名字：

-- 找出每个部门工资最高的员工（非递归 CTE）
WITH department_top AS (
    SELECT 
        department_id,
        MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
)
SELECT 
    e.name,
    e.department_id,
    e.salary,
    d.department_name
FROM employees e
JOIN department_top dt ON e.department_id = dt.department_id 
    AND e.salary = dt.max_salary
JOIN departments d ON e.department_id = d.id;

递归 CTE 才是真正的杀手级特性。它能处理层级数据——组织架构树、评论回复链、BOM 物料清单等场景：

-- 递归查询：从 CEO 开始展开完整组织架构（最多 10 层）
WITH RECURSIVE org_tree AS (
    -- 锚点：顶层（没有上级的员工）
    SELECT 
        id, name, manager_id, 
        1 AS level,
        CAST(name AS CHAR(1000)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归部分：查找下级
    SELECT 
        e.id, e.name, e.manager_id,
        ot.level + 1,
        CONCAT(ot.path, ' > ', e.name)
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
    WHERE ot.level < 10  -- 防止无限递归
)
SELECT 
    CONCAT(REPEAT('  ', level - 1), name) AS org_chart,
    level,
    path
FROM org_tree
ORDER BY path;

⚠️ **警告：**递归 CTE 必须设置终止条件（如 WHERE level < 10），否则遇到循环引用的脏数据会导致无限递归，最终耗尽内存导致 MySQL 崩溃。

💡 CTE vs 子查询 vs 临时表：如何选择

很多开发者纠结 CTE 和子查询的性能差异，下面是实测对比：

场景 CTE 子查询 临时表 推荐
单次引用、简单查询 ✅ 可读性好 ✅ 性能相同 ❌ 过度 子查询
多次引用同一结果集 ✅ 只计算一次 ❌ 可能重复执行 ✅ 适合大数据 CTE
递归查询（树形数据） ✅ 唯一选择 ❌ 无法实现 ⚠️ 需要存储过程 CTE
大数据量（>100 万行） ⚠️ 物化开销 ✅ 可能更优 ✅ 可加索引 临时表
需要索引优化 ❌ 无法建索引 ❌ 无法建索引 ✅ 可以建索引 临时表

💡 **提示：**MySQL 8.0.14 开始支持 CTE 的 MATERIALIZED / NOT MATERIALIZED 提示，可以控制 CTE 是物化为临时表还是展开为子查询。对于小数据集，NOT MATERIALIZED 通常更快。

🚀 二、窗口函数：在 SQL 层完成复杂分析

窗口函数（Window Functions）是 MySQL 8.0 的另一个重大升级。它在不折叠行的前提下进行分组计算——这正是 GROUP BY 做不到的。

📊 核心函数速览

-- 员工薪资分析：排名、占比、移动平均、前后值
SELECT 
    name,
    department_id,
    salary,
    -- 排名函数
    ROW_NUMBER() OVER w AS row_num,
    RANK() OVER w AS rank_num,
    DENSE_RANK() OVER w AS dense_rank_num,
    -- 聚合窗口函数
    SUM(salary) OVER (PARTITION BY department_id) AS dept_total,
    ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY department_id), 2) AS dept_pct,
    -- 偏移函数
    LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary,
    LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary,
    -- 累计与滑动窗口
    SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC)
ORDER BY department_id, salary DESC;

📌 记住：WINDOW 子句（MySQL 8.0.22+）可以给窗口定义起名字，避免在每个函数后面重复写 PARTITION BY ... ORDER BY ...，大幅提升 SQL 可读性。

🎯 ROW_NUMBER vs RANK vs DENSE_RANK：面试必考

这三个排名函数的区别是高频面试题，但很多人只记住了结论，没有理解本质差异。用一个例子说清楚：

-- 假设薪资为：10000, 10000, 8000, 8000, 7000
SELECT 
    name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
    RANK()       OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;
-- 结果：
-- 张三  10000  rn=1  rnk=1  drnk=1
-- 李四  10000  rn=2  rnk=1  drnk=1  -- 并列
-- 王五   8000  rn=3  rnk=3  drnk=2  -- RANK 跳过 2，DENSE_RANK 不跳
-- 赵六   8000  rn=4  rnk=3  drnk=2
-- 钱七   7000  rn=5  rnk=5  drnk=3

实战中最常用的场景是取每个分组的 Top N：

-- 每个部门薪资 Top 3 的员工
WITH ranked AS (
    SELECT 
        name, department_id, salary,
        ROW_NUMBER() OVER (
            PARTITION BY department_id 
            ORDER BY salary DESC
        ) AS rn
    FROM employees
)
SELECT name, department_id, salary
FROM ranked
WHERE rn <= 3;

💡 实战：连续登录天数统计

窗口函数的经典实战题——统计用户最长连续登录天数：

-- 方法：用日期减去行号，连续登录的日期会得到相同的差值
WITH login_groups AS (
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY login_date
        ) DAY) AS group_key
    FROM (
        -- 去重：同一天多次登录只算一次
        SELECT DISTINCT user_id, login_date
        FROM user_logins
    ) t
)
SELECT 
    user_id,
    MIN(login_date) AS start_date,
    MAX(login_date) AS end_date,
    COUNT(*) AS consecutive_days
FROM login_groups
GROUP BY user_id, group_key
HAVING COUNT(*) >= 3  -- 至少连续 3 天
ORDER BY consecutive_days DESC;

⚠️ **警告：**窗口函数不能用在 WHERE 子句中。如果需要过滤窗口函数的结果，必须用 CTE 或子查询包一层，然后在外层 WHERE 过滤。这是新手最常犯的错误。

🔧 三、JSON 函数：在数据库层处理结构化数据

MySQL 8.0 大幅增强了 JSON 处理能力，引入了 JSON_TABLE、多值索引（Multi-Valued Index）等重量级特性。对于很多场景，你不再需要把 JSON 数据拉到应用层再解析。

📋 JSON 路径查询与修改

MySQL 的 JSON 路径语法使用 $ 作为根节点，支持数组下标和成员访问：

-- 创建包含 JSON 列的表
CREATE TABLE api_responses (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    request_url VARCHAR(500) NOT NULL,
    response_body JSON NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_at)
);

-- 插入测试数据
INSERT INTO api_responses (request_url, response_body) VALUES
('/api/users/1', '{"name":"张三","age":28,"tags":["admin","developer"],"address":{"city":"北京","district":"海淀"}}'),
('/api/users/2', '{"name":"李四","age":35,"tags":["viewer"],"address":{"city":"上海","district":"浦东"}}');

-- JSON 路径查询
SELECT 
    request_url,
    JSON_EXTRACT(response_body, '$.name') AS name,           -- "张三"
    response_body->'$.address.city' AS city,                  -- "北京"（-> 等价于 JSON_EXTRACT）
    response_body->>'$.address.city' AS city_unquoted,        -- 北京（->> 去掉引号）
    JSON_LENGTH(response_body->'$.tags') AS tag_count,        -- 2
    JSON_CONTAINS(response_body->'$.tags', '"admin"', '$') AS is_admin  -- 1
FROM api_responses;

💡 提示：-> 返回带引号的 JSON 值，->> 返回去掉引号的纯文本。在 WHERE 条件中做字符串比较时，记得用 ->> 而不是 ->，否则索引不会生效。

🚀 JSON_TABLE：把 JSON 数组展开为行

JSON_TABLE 是 MySQL 8.0 最强大的 JSON 函数，它能把 JSON 数组"展开"为关系表，从而可以用标准 SQL 进行 JOIN、聚合等操作：

-- 订单表：items 是 JSON 数组
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_name VARCHAR(100),
    items JSON NOT NULL
);

INSERT INTO orders VALUES
(1, '张三', '[{"product":"iPhone 16","qty":1,"price":5999},{"product":"AirPods","qty":2,"price":999}]'),
(2, '李四', '[{"product":"MacBook Pro","qty":1,"price":14999}]'),
(3, '王五', '[{"product":"iPad","qty":1,"price":3999},{"product":"Apple Pencil","qty":1,"price":999},{"product":"Smart Cover","qty":1,"price":299}]');

-- 用 JSON_TABLE 把订单明细展开为行
SELECT 
    o.id AS order_id,
    o.customer_name,
    jt.product,
    jt.qty,
    jt.price,
    jt.qty * jt.price AS subtotal
FROM orders o,
JSON_TABLE(
    o.items,
    '$[*]' COLUMNS (
        product VARCHAR(100) PATH '$.product',
        qty     INT          PATH '$.qty',
        price   DECIMAL(10,2) PATH '$.price'
    )
) AS jt
ORDER BY o.id;

结合聚合函数，可以直接在 SQL 层完成 JSON 数据的统计分析：

-- 每个订单的总金额和商品数
SELECT 
    o.id,
    o.customer_name,
    COUNT(*) AS item_count,
    SUM(jt.qty * jt.price) AS order_total
FROM orders o,
JSON_TABLE(
    o.items,
    '$[*]' COLUMNS (
        qty   INT           PATH '$.qty',
        price DECIMAL(10,2) PATH '$.price'
    )
) AS jt
GROUP BY o.id, o.customer_name
ORDER BY order_total DESC;

📊 多值索引：为 JSON 数组建立索引

MySQL 8.0.17 引入的多值索引（Multi-Valued Index）是真正改变游戏规则的特性。它能对 JSON 数组中的每个元素建立索引，让 JSON_CONTAINS、MEMBER OF、JSON_OVERLAPS 查询走索引扫描：

-- 给 tags 数组建立多值索引
ALTER TABLE api_responses ADD INDEX idx_tags (
    (CAST(response_body->'$.tags' AS CHAR(50) ARRAY))
);

-- 现在这两个查询都能走索引！
-- 方式 1：MEMBER OF
SELECT * FROM api_responses WHERE 'admin' MEMBER OF (response_body->'$.tags');

-- 方式 2：JSON_CONTAINS
SELECT * FROM api_responses WHERE JSON_CONTAINS(response_body->'$.tags', '"viewer"');

-- 方式 3：JSON_OVERLAPS（交集判断）
SELECT * FROM api_responses WHERE JSON_OVERLAPS(
    response_body->'$.tags', '["admin", "superuser"]'
);

⚠️ **警告：**多值索引只支持 CHAR、VARCHAR、NUMERIC（含 DECIMAL、INTEGER）类型的数组。如果你的 JSON 数组包含嵌套对象，多值索引不适用，需要在应用层处理或使用生成列（Generated Column）。

🔧 JSON 构造与聚合：在 SQL 层组装 JSON

除了查询和解析 JSON，MySQL 8.x 还能在 SQL 层直接构造 JSON 响应，这在构建 REST API 时非常有用——直接从数据库返回 JSON 格式的结果，省去应用层的序列化开销：

-- 用 JSON_OBJECT 和 JSON_ARRAYAGG 构造嵌套 JSON 响应
SELECT 
    d.name AS department,
    JSON_OBJECT(
        'id', d.id,
        'name', d.name,
        'employees', JSON_ARRAYAGG(
            JSON_OBJECT(
                'name', e.name,
                'salary', e.salary,
                'tags', IFNULL(e.tags, JSON_ARRAY())
            )
        )
    ) AS department_json
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name;

JSON_OBJECTAGG 则可以用某个字段作为 key，直接构造字典结构：

-- 用 JSON_OBJECTAGG 构造 {城市: 人数} 的统计字典
SELECT 
    JSON_OBJECTAGG(
        city, user_count
    ) AS city_distribution
FROM (
    SELECT city, COUNT(*) AS user_count
    FROM users
    GROUP BY city
) t;
-- 结果示例：{"北京": 1250, "上海": 980, "深圳": 756, "杭州": 523}

💡 **提示：**如果你的后端框架（如 Spring Boot、Express）需要返回嵌套 JSON，直接在 SQL 层用 JSON_OBJECT + JSON_ARRAYAGG 构造比在应用层拼装更高效，尤其是在数据量大时，可以显著减少数据传输量和应用层内存占用。

⚠️ JSON 生成列 + 索引：另一种优化路径

对于频繁查询的 JSON 字段，生成列（Generated Column）是更通用的方案：

-- 创建虚拟生成列并建立索引
ALTER TABLE api_responses
ADD COLUMN city VARCHAR(50) 
    GENERATED ALWAYS AS (response_body->>'$.address.city') VIRTUAL,
ADD INDEX idx_city (city);

-- 查询走索引
SELECT * FROM api_responses WHERE city = '北京';

-- 查看实际执行计划
EXPLAIN SELECT * FROM api_responses WHERE city = '北京';
-- type: ref, key: idx_city ✅

💡 提示：VIRTUAL 生成列不占用存储空间，只在查询时计算。如果 JSON 字段很大且查询频繁，可以用 STORED 生成列换取查询性能（牺牲写入性能和存储空间）。

⚠️ 四、生产环境避坑指南

在生产环境使用这些高级特性时，有几个常见的坑需要注意。

坑点 1：窗口函数的隐式排序

-- ❌ 错误写法：依赖窗口函数的隐式排序
SELECT name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;
-- MySQL 8.0.2 之前，窗口函数的 ORDER BY 不影响最终结果的排序！

-- ✅ 正确写法：显式指定最终排序
SELECT name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
ORDER BY rn;  -- 必须显式排序

坑点 2：CTE 的物化陷阱

-- ❌ 低效写法：CTE 被物化为临时表，无法利用外层索引
WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE id = 12345;

-- ✅ 高效写法：用 NOT MATERIALIZED 让优化器展开为子查询
WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users AS au WHERE au.id = 12345;
-- MySQL 优化器可能会自动将此转换为带 WHERE 条件的子查询

坑点 3：JSON 函数的性能边界

操作 数据量 性能表现 建议
JSON_EXTRACT 单字段查询 <10 万行 ✅ 快速 直接使用
JSON_TABLE 展开数组 <1 万行 ✅ 可接受 直接使用
JSON_TABLE 展开数组 >10 万行 ❌ 很慢 用生成列或拆表
JSON_CONTAINS 无索引 >1 万行 ❌ 全表扫描 必须加多值索引
嵌套 JSON（3 层以上） 任意 ⚠️ 维护困难 考虑拆分为独立表

⚠️ **警告：**JSON 列上无法直接建普通 B+树索引。如果你的查询模式是固定的（比如总是按 $.status 过滤），用生成列 + 索引比多值索引更高效。多值索引只适用于数组场景。

📊 五、综合实战：用 CTE + 窗口函数 + JSON 实现用户行为分析

把三个特性结合起来，实现一个真实的业务场景——分析用户的购买行为模式：

-- 用户购买行为分析：RFM 模型评分
WITH user_orders AS (
    -- 第一步：JSON_TABLE 展开订单明细，关联用户信息
    SELECT 
        u.id AS user_id,
        u.name,
        o.id AS order_id,
        o.created_at AS order_date,
        SUM(jt.qty * jt.price) AS order_amount
    FROM users u
    JOIN orders o ON u.id = o.user_id,
    JSON_TABLE(
        o.items,
        '$[*]' COLUMNS (
            qty   INT           PATH '$.qty',
            price DECIMAL(10,2) PATH '$.price'
        )
    ) AS jt
    GROUP BY u.id, u.name, o.id, o.created_at
),
rfm_base AS (
    -- 第二步：计算 RFM 基础指标
    SELECT 
        user_id,
        name,
        DATEDIFF(CURRENT_DATE, MAX(order_date)) AS recency,
        COUNT(DISTINCT order_id) AS frequency,
        SUM(order_amount) AS monetary
    FROM user_orders
    GROUP BY user_id, name
),
rfm_scored AS (
    -- 第三步：用窗口函数分位数评分
    SELECT 
        *,
        NTILE(5) OVER (ORDER BY recency DESC) AS r_score,
        NTILE(5) OVER (ORDER BY frequency) AS f_score,
        NTILE(5) OVER (ORDER BY monetary) AS m_score
    FROM rfm_base
)
-- 第四步：输出最终结果
SELECT 
    user_id,
    name,
    recency,
    frequency,
    monetary,
    r_score,
    f_score,
    m_score,
    CONCAT(r_score, f_score, m_score) AS rfm_cell,
    CASE 
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN '🏆 高价值用户'
        WHEN r_score >= 4 AND f_score >= 2 THEN '📈 活跃用户'
        WHEN r_score <= 2 AND f_score >= 3 THEN '⚠️ 流失风险用户'
        WHEN r_score <= 2 AND f_score <= 2 THEN '💤 沉睡用户'
        ELSE '🔄 一般用户'
    END AS user_segment
FROM rfm_scored
ORDER BY (r_score + f_score + m_score) DESC;

这个查询用 CTE 分步骤构建分析流水线，用 JSON_TABLE 展开 JSON 订单明细，用 NTILE 窗口函数进行分位数评分——三者配合，一个 SQL 完成了传统方案需要多条 SQL + 应用层处理才能完成的 RFM 分析。

✅ 总结与建议

MySQL 8.x 的高级查询特性已经让它具备了与 PostgreSQL 媲美的分析能力。以下是实际使用建议：

  • ✅ CTE 用于替代 2 层以上的子查询嵌套，提升可读性
  • ✅ 窗口函数 用于排名、累计、同比环比等分析场景，避免 GROUP BY 丢失明细
  • ✅ JSON 函数 用于半结构化数据的存储和查询，但不要滥用——超过 3 层嵌套应该拆表
  • ✅ 多值索引 用于 JSON 数组的精确查询，但要注意只支持标量类型数组
  • ❌ 不要 在大表（>100 万行）上频繁使用 JSON_TABLE，性能会急剧下降
  • ❌ 不要 用 JSON 列替代所有关系型设计——JSON 适合灵活的扩展字段，不适合核心业务数据

⚡ **关键结论：**MySQL 8.x 的高级查询能力足以覆盖 90% 的分析需求。善用 CTE + 窗口函数 + JSON 函数的组合，很多过去需要拉到应用层处理的逻辑，现在一条 SQL 就能搞定，减少了数据传输开销和代码复杂度。

相关工具推荐：

📚 相关文章

PostgreSQL JSONB 高级查询实战：索引策略、查询优化与生产级数据建模

2026-07-04·18 分钟

SQL 优化技巧大全：让你的查询快 10 倍

2026-06-21·10 分钟

数据库设计三大范式：从需求分析到 ER 图

2026-06-20·10 分钟