【MySQL筑基篇】从排名统计到非结构化存储：MySQL窗口函数与JSON实战教程-平芜编程栈

🍃 予枫：个人主页
📚 个人专栏: 《Java 从入门到起飞》《读研码农的干货日常》💻 Debug 这个世界，Return 更好的自己！

引言

还在认为MySQL只能存储简单行数据？大错特错！在现代开发中，窗口函数能轻松搞定复杂排名、分组统计，JSON数据类型可灵活处理非结构化数据，这两大高级特性早已成为后端开发者提升效率的利器。本文带你吃透这两个核心技能，摆脱“MySQL只会CRUD”的标签，轻松应对复杂业务场景！

文章目录

引言
一、MYSQL高级特性：不止于简单存储
二、窗口函数：复杂统计场景的“杀手锏”
- 2.1 什么是窗口函数？
- 2.2 常用窗口函数分类与语法
- - 2.2.1 函数分类（3大类核心）
  - 2.2.2 基础语法
- 2.3 实战案例：窗口函数解决实际业务问题
- - 案例1：员工薪资排名（排序类函数）
  - 案例2：部门薪资统计（聚合类函数）
三、JSON数据类型：非结构化数据的“灵活存储方案”
- 3.1 为什么需要JSON数据类型？
- 3.2 JSON数据类型核心操作
- - 3.2.1 数据插入（两种方式）
  - 3.2.2 数据查询（精准定位JSON字段）
  - 3.2.3 数据修改（JSON字段局部更新）
- 3.3 实战场景：JSON存储用户配置信息
四、总结

一、MYSQL高级特性：不止于简单存储

在传统认知中，MySQL常被当作“简单的关系型数据库”，仅用于存储规整的行数据。但随着业务场景的复杂化，仅靠基础的CRUD操作早已无法满足需求。窗口函数（Window Functions）和JSON数据类型支持，正是MySQL为适配现代开发推出的核心高级特性，既能解决复杂的数据统计问题，又能灵活应对非结构化数据存储需求。

👍 建议收藏本文，后续实操时直接查阅！

二、窗口函数：复杂统计场景的“杀手锏”

2.1 什么是窗口函数？

窗口函数，也叫分析函数，是MySQL 8.0及以上版本引入的重要特性。它能在不压缩结果集的前提下，对数据进行分组、排序和聚合计算，相当于为每一行数据“开一个窗口”，在窗口内进行统计分析。

与传统聚合函数（SUM、AVG、COUNT等）相比，窗口函数最大的优势的是：计算后不会合并行数据，每一行都会保留原始信息，同时新增统计结果列。这在需要展示原始数据+统计信息的场景中（如：展示每个员工信息+所在部门平均薪资），比聚合函数更高效、更简洁。

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;