【MySQL筑基篇】从排名统计到非结构化存储：MySQL窗口函数与JSON实战教程
🍃 予枫：个人主页
📚 个人专栏: 《Java 从入门到起飞》《读研码农的干货日常》
💻 Debug 这个世界，Return 更好的自己！
引言
还在认为MySQL只能存储简单行数据？大错特错！在现代开发中，窗口函数能轻松搞定复杂排名、分组统计，JSON数据类型可灵活处理非结构化数据，这两大高级特性早已成为后端开发者提升效率的利器。本文带你吃透这两个核心技能，摆脱“MySQL只会CRUD”的标签，轻松应对复杂业务场景！
文章目录
一、MYSQL高级特性：不止于简单存储
在传统认知中，MySQL常被当作“简单的关系型数据库”，仅用于存储规整的行数据。但随着业务场景的复杂化，仅靠基础的CRUD操作早已无法满足需求。窗口函数（Window Functions）和JSON数据类型支持，正是MySQL为适配现代开发推出的核心高级特性，既能解决复杂的数据统计问题，又能灵活应对非结构化数据存储需求。
👍 建议收藏本文，后续实操时直接查阅！
二、窗口函数：复杂统计场景的“杀手锏”
2.1 什么是窗口函数？
窗口函数，也叫分析函数，是MySQL 8.0及以上版本引入的重要特性。它能在不压缩结果集的前提下，对数据进行分组、排序和聚合计算，相当于为每一行数据“开一个窗口”，在窗口内进行统计分析。
与传统聚合函数（SUM、AVG、COUNT等）相比，窗口函数最大的优势的是：计算后不会合并行数据，每一行都会保留原始信息，同时新增统计结果列。这在需要展示原始数据+统计信息的场景中（如：展示每个员工信息+所在部门平均薪资），比聚合函数更高效、更简洁。
💡 核心对比：PARTITION BY 与 GROUP BY 的本质区别
很多同学容易把窗口函数里的 PARTITION BY 和传统的 GROUP BY 混淆，记住最核心的一点：是否合并数据行。
- GROUP BY：按字段分组后，相同字段的数据会“折叠”成一行，原始的明细数据就不见了。
- PARTITION BY：虽然也是分组，但原始数据行数不变。它是在这个分组的范围内进行统计计算，并把结果附加在每一行明细数据的后面。
这也就是为什么在需要同时展示“明细数据”和“统计信息”的场景下，窗口函数比传统聚合函数更高效、更优雅！
2.2 常用窗口函数分类与语法
2.2.1 函数分类（3大类核心）
💡 核心分类，一目了然：
- 排序类：RANK()、DENSE_RANK()、ROW_NUMBER()
- 聚合类：SUM()、AVG()、COUNT()、MAX()、MIN()
- 分析类：LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()
2.2.2 基础语法
函数名(字段名) OVER ( PARTITION BY 分组字段 -- 可选，类似GROUP BY ORDER BY 排序字段 [ASC/DESC] -- 可选，窗口内排序 ROWS/RANGE BETWEEN 起始位置 AND 结束位置 -- 可选，窗口范围 ) AS 别名 2.3 实战案例：窗口函数解决实际业务问题
案例1：员工薪资排名（排序类函数）
需求：查询所有员工信息，并显示其所在部门的薪资排名（相同薪资排名相同，不占用后续名次）。
准备数据：
CREATE TABLE emp ( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(20) NOT NULL, dept_id INT NOT NULL, salary DECIMAL(10,2) NOT NULL ); INSERT INTO emp (emp_name, dept_id, salary) VALUES ('张三', 1, 8000.00), ('李四', 1, 9500.00), ('王五', 1, 8000.00), ('赵六', 2, 7500.00), ('孙七', 2, 10000.00); 查询SQL（使用DENSE_RANK()函数）：
SELECT emp_id, emp_name, dept_id, salary, DENSE_RANK() OVER ( PARTITION BY dept_id ORDER BY salary DESC ) AS dept_salary_rank FROM emp; 查询结果：
| emp_id | emp_name | dept_id | salary | dept_salary_rank |
|---|---|---|---|---|
| 2 | 李四 | 1 | 9500.00 | 1 |
| 1 | 张三 | 1 | 8000.00 | 2 |
| 3 | 王五 | 1 | 8000.00 | 2 |
| 5 | 孙七 | 2 | 10000.00 | 1 |
| 4 | 赵六 | 2 | 7500.00 | 2 |
案例2：部门薪资统计（聚合类函数）
需求：查询每个员工的薪资，并显示所在部门的薪资总和、平均薪资。
SQL语句：
SELECT emp_id, emp_name, dept_id, salary, SUM(salary) OVER (PARTITION BY dept_id) AS dept_salary_total, AVG(salary) OVER (PARTITION BY dept_id) AS dept_salary_avg FROM emp; ✅ 对比传统写法（子查询/关联查询），窗口函数无需多表关联，代码更简洁，执行效率更高！
三、JSON数据类型：非结构化数据的“灵活存储方案”
3.1 为什么需要JSON数据类型？
在现代开发中，经常会遇到非结构化或半结构化数据（如：用户画像、接口返回数据、配置信息等）。如果用传统的字段存储，会存在以下问题：
- 字段数量不确定，难以设计表结构；
- 数据格式灵活多变，新增字段需修改表结构；
- 数据查询和解析繁琐。
MySQL 5.7及以上版本引入的JSON数据类型，完美解决了这些问题：无需固定表结构，可灵活存储复杂数据，同时支持JSON数据的快速查询和操作。
3.2 JSON数据类型核心操作
3.2.1 数据插入（两种方式）
方式1：直接插入JSON格式字符串
CREATE TABLE user_profile ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL UNIQUE, profile JSON NOT NULL -- JSON类型字段 ); INSERT INTO user_profile (user_id, profile) VALUES (1, '{"name":"张三","age":25,"hobbies":["篮球","编程"],"address":{"province":"广东","city":"深圳"}}'); 方式2：使用JSON_OBJECT()函数构造JSON
INSERT INTO user_profile (user_id, profile) VALUES (2, JSON_OBJECT( 'name', '李四', 'age', 28, 'hobbies', JSON_ARRAY('足球','阅读'), 'address', JSON_OBJECT('province','浙江','city','杭州') )); 3.2.2 数据查询（精准定位JSON字段）
MySQL提供了多种JSON查询函数，核心常用的有：
- -> ：提取JSON对象字段（返回带引号的字符串）
- ->> ：提取JSON对象字段（返回无引号的字符串）
- JSON_EXTRACT(json字段, ‘$.路径’) ：通用提取函数
- JSON_CONTAINS(json字段, ‘值’, ‘$.路径’) ：判断是否包含指定值
示例：查询用户姓名、所在城市、爱好列表
SELECT user_id, profile->>'$.name' AS user_name, profile->>'$.address.city' AS city, profile->>'$.hobbies' AS hobbies, JSON_EXTRACT(profile, '$.age') AS age FROM user_profile; 查询结果：
| user_id | user_name | city | hobbies | age |
|---|---|---|---|---|
| 1 | 张三 | 深圳 | [“篮球”, “编程”] | 25 |
| 2 | 李四 | 杭州 | [“足球”, “阅读”] | 28 |
3.2.3 数据修改（JSON字段局部更新）
无需更新整个JSON对象，可精准修改指定字段：
-- 修改用户年龄 UPDATE user_profile SET profile = JSON_SET(profile, '$.age', 26) WHERE user_id = 1; -- 新增用户职业字段 UPDATE user_profile SET profile = JSON_INSERT(profile, '$.job', '程序员') WHERE user_id = 1; 3.3 实战场景：JSON存储用户配置信息
需求：存储用户的系统配置（主题、通知开关、默认页面等），配置项可能随时新增，无需修改表结构。
使用JSON类型存储后，可轻松实现配置的新增、修改、查询，无需担心字段扩展性问题，比传统多字段存储更灵活。
四、总结
本文重点讲解了MySQL的两大高级特性：窗口函数和JSON数据类型支持。窗口函数打破了传统聚合函数的限制，能高效解决复杂的分组统计、排序问题；JSON数据类型则为非结构化数据提供了灵活的存储方案，适配现代开发中多变的数据场景。
MySQL早已不是“只能存简单行数据”的数据库，熟练掌握这些高级特性，能显著提升开发效率，轻松应对复杂业务需求。建议大家在实际项目中多实操练习，将这些技能融入到业务开发中。
