核心内容摘要
午夜的迷迭香：探寻三上悠亚的独特魅力
予枫个人主页 个人专栏: 《Java 从入门到起飞》《读研码农的干货日常》 Debug 这个世界Return 更好的自己引言还在认为MySQL只能存储简单行数据大错特错在现代开发中窗口函数能轻松搞定复杂排名、分组统计JSON数据类型可灵活处理非结构化数据这两大高级特性早已成为后端开发者提升效率的利器。
本文带你吃透这两个核心技能摆脱“MySQL只会CRUD”的标签轻松应对复杂业务场景文章目录引言
MYSQL高级特性不止于简单存储
窗口函数复杂统计场景的“杀手锏”
1 什么是窗口函数
2 常用窗口函数分类与语法
2.
1 函数分类3大类核心
2.
2 基础语法
3 实战案例窗口函数解决实际业务问题案例1员工薪资排名排序类函数案例2部门薪资统计聚合类函数
JSON数据类型非结构化数据的“灵活存储方案”
1 为什么需要JSON数据类型
2 JSON数据类型核心操作
3.
1 数据插入两种方式
3.
2 数据查询精准定位JSON字段
3.
3 数据修改JSON字段局部更新
3 实战场景JSON存储用户配置信息
四、
总结
MYSQL高级特性不止于简单存储在传统认知中MySQL常被当作“简单的关系型数据库”仅用于存储规整的行数据。
但随着业务场景的复杂化仅靠基础的CRUD操作早已无法满足需求。
窗口函数Window Functions和JSON数据类型支持正是MySQL为适配现代开发推出的核心高级特性既能解决复杂的数据统计问题又能灵活应对非结构化数据存储需求。
建议收藏本文后续实操时直接查阅
窗口函数复杂统计场景的“杀手锏”
1 什么是窗口函数窗口函数也叫分析函数是MySQL
0及以上版本引入的重要特性。
它能在不压缩结果集的前提下对数据进行分组、排序和聚合计算相当于为每一行数据“开一个窗口”在窗口内进行统计分析。
与传统聚合函数SUM、AVG、COUNT等相比窗口函数最大的优势的是计算后不会合并行数据每一行都会保留原始信息同时新增统计结果列。
这在需要展示原始数据统计信息的场景中如展示每个员工信息所在部门平均薪资比聚合函数更高效、更简洁。
2 常用窗口函数分类与语法
2.
1 函数分类3大类核心 核心分类一目了然排序类RANK()、DENSE_RANK()、ROW_NUMBER()聚合类SUM()、AVG()、COUNT()、MAX()、MIN()分析类LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()
2.
2 基础语法函数名(字段名) OVER ( PARTITION BY 分组字段 -- 可选类似GROUP BY ORDER BY 排序字段 [ASC/DESC] -- 可选窗口内排序 ROWS/RANGE BETWEEN 起始位置 AND 结束位置 -- 可选窗口范围 ) AS 别名
3 实战案例窗口函数解决实际业务问题案例1员工薪资排名排序类函数需求查询所有员工信息并显示其所在部门的薪资排名相同薪资排名相同不占用后续名次。
准备数据CREATE TABLE emp ( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(
NOT NULL, dept_id INT NOT NULL, salary DECIMAL(10,
NOT NULL ); INSERT INTO emp (emp_name, dept_id, salary) VALUES (张三, 1,
8000.
, (李四, 1,
9500.
, (王五, 1,
8000.
, (赵六, 2,
7500.
, (孙七, 2,
10000.
;查询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_idemp_namedept_idsalarydept_salary_rank2李四
1
0011张三
1
0023王五
1
0025孙七
2
0014赵六
2
002案例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数据类型非结构化数据的“灵活存储方案”
1 为什么需要JSON数据类型在现代开发中经常会遇到非结构化或半结构化数据如用户画像、接口返回数据、配置信息等。
如果用传统的字段存储会存在以下问题字段数量不确定难以设计表结构数据格式灵活多变新增字段需修改表结构数据查询和解析繁琐。
MySQL
7及以上版本引入的JSON数据类型完美解决了这些问题无需固定表结构可灵活存储复杂数据同时支持JSON数据的快速查询和操作。
2 JSON数据类型核心操作
3.
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()函数构造JSONINSERT INTO user_profile (user_id, profile) VALUES (2, JSON_OBJECT( name, 李四, age, 28, hobbies, JSON_ARRAY(足球,阅读), address, JSON_OBJECT(province,浙江,city,杭州) ));
3.
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_iduser_namecityhobbiesage1张三深圳[“篮球”, “编程”]252李四杭州[“足球”, “阅读”]
283.
3 数据修改JSON字段局部更新无需更新整个JSON对象可精准修改指定字段-- 修改用户年龄 UPDATE user_profile SET profile JSON_SET(profile, $.age,
WHERE user_id 1; -- 新增用户职业字段 UPDATE user_profile SET profile JSON_INSERT(profile, $.job, 程序员) WHERE user_id 1;
3 实战场景JSON存储用户配置信息需求存储用户的系统配置主题、通知开关、默认页面等配置项可能随时新增无需修改表结构。
使用JSON类型存储后可轻松实现配置的新增、修改、查询无需担心字段扩展性问题比传统多字段存储更灵活。
四、
总结本文重点讲解了MySQL的两大高级特性窗口函数和JSON数据类型支持。
窗口函数打破了传统聚合函数的限制能高效解决复杂的分组统计、排序问题JSON数据类型则为非结构化数据提供了灵活的存储方案适配现代开发中多变的数据场景。
MySQL早已不是“只能存简单行数据”的数据库熟练掌握这些高级特性能显著提升开发效率轻松应对复杂业务需求。
建议大家在实际项目中多实操练习将这些技能融入到业务开发中。