Mysql新特性

Blog Author

八月 03, 2025 • 0 Comments

MySQL 新特性深入讲解

一、JSON 类型

1. 引言：打破结构化桎梏的利器

在传统关系型数据库中，数据表结构必须事先定义好字段类型与结构，这种“强结构约束”虽保障了数据规范性，却在业务快速变化时显得力不从心。特别是当字段经常变动、字段存在多样化或部分缺失时，频繁修改表结构会显著增加开发与维护成本。

从 MySQL 5.7 开始引入的 JSON 类型，为开发者带来了接近 NoSQL 的灵活性，而到了 MySQL 8.0，其性能与日志处理也得到了显著优化，让 JSON 成为了真正可用于生产环境的数据类型。

2. JSON 类型基本形态

✅ JSON 对象（key-value）

{"name": "骆昊", "tel": "13122335566", "QQ": "957658"}

✅ JSON 数组（列表结构）

[1, 2, 3]

[
  {"name": "骆昊", "tel": "13122335566"},
  {"name": "王大锤", "QQ": "123456"}
]

3. 应用场景：多渠道登录信息存储

传统设计会为每种登录方式单独建字段，如下：

CREATE TABLE user_login (
  user_id BIGINT,
  tel VARCHAR(20),
  wechat VARCHAR(50),
  qq VARCHAR(20),
  weibo VARCHAR(50)
);

但这意味着：

大量字段是 NULL；
新增一种登录方式必须 改表结构。

JSON 优化方案：

CREATE TABLE tb_test (
  user_id BIGINT UNSIGNED,
  login_info JSON,
  PRIMARY KEY (user_id)
);


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

查询手机号和微信：

SELECT 
  user_id,
  login_info ->> '$.tel' AS 手机号,
  login_info ->> '$.wechat' AS 微信 
FROM tb_test;

4. 进阶案例：用户画像 + 标签推荐系统

📌 标签定义表：

CREATE TABLE tb_tags (
  tag_id INT UNSIGNED NOT NULL,
  tag_name VARCHAR(20) NOT NULL,
  PRIMARY KEY (tag_id)
);

📌 用户打标签：

CREATE TABLE tb_users_tags (
  user_id BIGINT UNSIGNED NOT NULL,
  user_tags JSON NOT NULL
);


INSERT INTO tb_users_tags VALUES
(1, '[2, 6, 8, 10]'),
(2, '[3, 10, 12]'),
(3, '[3, 8, 9, 11]');

🔍 实用查询：

查“爱看电影”用户（标签ID为10）：

SELECT user_id
FROM tb_users_tags
WHERE 10 MEMBER OF (user_tags -> '$');

查“爱看电影且是80后”用户：

SELECT user_id
FROM tb_users_tags
WHERE JSON_CONTAINS(user_tags -> '$', '[2, 10]');

查“爱看电影或 80后或 90后”用户：

SELECT user_id
FROM tb_users_tags
WHERE JSON_OVERLAPS(user_tags -> '$', '[2, 3, 10]');

5. 最佳实践总结

场景	推荐使用 JSON 类型的理由
用户自定义字段	字段内容差异大，结构不可预测
多渠道授权/登录信息存储	结构灵活，不必频繁改表结构
用户画像/标签/权限管理等数组	可用 JSON 数组方便存储和查询
API 响应缓存	JSON 与前端直接对接，无需映射转换

二、窗口函数（Window Functions）

1. 引言：OLAP 场景的核心利器

窗口函数是在不聚合记录的情况下执行聚合操作的强大工具。它允许对查询结果中每一行进行分析，并能返回跨行的结果，常用于排名、累计、前后值对比等分析场景。

2. 语法结构讲解

<窗口函数> OVER (
  PARTITION BY <分组列>
  ORDER BY <排序列>
  ROWS|RANGE BETWEEN <起始> AND <结束>
)

✅ 常用窗口函数分类：

函数类别	示例
排名相关	`row_number()`、`rank()`、`dense_rank()`
相邻行对比	`lead()`、`lag()`
聚合类扩展	`sum()`、`avg()`、`count()` 等

3. 应用场景一：筛选指定排名区间的记录

查询薪资排名第4\~第6名员工：

SELECT * 
FROM (
    SELECT ename, sal,
           ROW_NUMBER() OVER (ORDER BY sal DESC) AS rk
    FROM tb_emp
) temp
WHERE rk BETWEEN 4 AND 6;

✅ 适用于排行榜、分页、Top-N 分析等。

4. 应用场景二：分组内 Top-N 分析

每个部门薪资前两名的员工：

SELECT ename, sal, dname
FROM (
    SELECT ename, sal, dno,
           RANK() OVER (PARTITION BY dno ORDER BY sal DESC) AS rnk
    FROM tb_emp
) temp
NATURAL JOIN tb_dept
WHERE rnk <= 2;

✅ 适用于分组内排序、组内 Top 分析、组内累计等。

5. 窗口函数 vs 聚合函数

特性	窗口函数	聚合函数
是否改变记录条数	❌ 保持记录数	✅ 聚合为一行
计算方式	每行在自己的“窗口”中计算	多行合并为单个结果
使用场景	排名、对比、累计、分组分析	总和、平均、计数、最值等整体统计

6. 最佳实践与注意事项

性能优化建议：
使用索引支持的 PARTITION BY 和 ORDER BY。
避免在大表中嵌套多层窗口函数。
与分页结合：
ROW_NUMBER() 常与 LIMIT 和外层查询组合使用实现精确分页。
组合使用：
可结合 CASE WHEN 实现分组内多条件累计。

三、总结：JSON 类型 + 窗口函数 = MySQL 现代数据分析双引擎

功能维度	JSON 类型	窗口函数
类型特点	存储结构化/半结构化数据	在分组窗口上执行分析函数
使用场景	多字段合一、可变字段、嵌套数据结构	分组排序、排名、Top-N、累计等 OLAP 分析
配套函数	`->>`、`JSON_EXTRACT`、`JSON_CONTAINS` 等	`RANK()`、`ROW_NUMBER()`、`SUM() OVER()`
推荐版本	MySQL 5.7 及以上（8.0 最佳）	MySQL 8.0 及以上

如需我继续讲解其它新特性（如 CTE、角色权限、GIS 类型等），欢迎继续提问！

Python520