MySQL 新特性

JSON 类型

存储 JSON 数据

CREATE TABLE tb_user (
    user_id    BIGINT UNSIGNED PRIMARY KEY,
    login_info JSON COMMENT '登录方式信息'
);

INSERT INTO tb_user VALUES
    (1, '{"tel": "13122335566", "QQ": "654321", "wechat": "jackfrued"}'),
    (2, '{"tel": "13599876543", "weibo": "wangdachui123"}');

查询 JSON 字段

-- JSON_EXTRACT + JSON_UNQUOTE（MySQL 5.7+）
SELECT user_id,
       JSON_UNQUOTE(JSON_EXTRACT(login_info, '$.tel')) AS 手机号
  FROM tb_user;

-- 简写形式（MySQL 5.7+）
SELECT user_id,
       login_info ->> '$.tel' AS 手机号,
       login_info ->> '$.wechat' AS 微信
  FROM tb_user;

JSON 函数

窗口函数（MySQL 8.0+）

窗口函数对一组行计算并返回结果，不减少行数。

SELECT stu_name, cou_name, score,
       RANK()       OVER (PARTITION BY cou_name ORDER BY score DESC) AS ranking,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_ranking,
       ROW_NUMBER()  OVER (ORDER BY score DESC) AS row_num,
       AVG(score) OVER (PARTITION BY cou_name) AS avg_score,
       SUM(score) OVER (ORDER BY stu_name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sliding_sum
  FROM tb_record r
 INNER JOIN tb_student s ON r.stu_id = s.stu_id
 INNER JOIN tb_course c ON r.cou_id = c.cou_id;

常用窗口函数

帧（Frame）

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- 从开始到当前行
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING          -- 前后各1行
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- 全窗口

CTE（公用表表达式，MySQL 8.0+）

-- 普通CTE（简化子查询）
WITH cte AS (
    SELECT cou_id, COUNT(*) AS cnt FROM tb_record GROUP BY cou_id
)
SELECT * FROM cte WHERE cnt > 5;

-- 递归CTE（生成序列）
WITH RECURSIVE cte (n) AS (
    SELECT 1           -- 起始
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 10  -- 递归
)
SELECT * FROM cte;  -- 生成 1~10

CTAS（CREATE TABLE ... AS SELECT）

CREATE TABLE tb_grade_avg AS
SELECT cou_id, AVG(score) AS avg_score
  FROM tb_record
 GROUP BY cou_id;

总结

• JSON：灵活存储变长字段，->> 可直接提取值
• 窗口函数：保留行、排名、累计计算，OVER(PARTITION BY ... ORDER BY ...)
• CTE：WITH cte AS (...) 简化复杂查询；递归CTE可生成序列
• CTAS：从查询结果快速建表