SQL详解之MySQL新特性

上一节课我们讲解了DCL的权限管理操作，本节课将聚焦MySQL的两大核心新特性——JSON类型和窗口函数，这两个特性极大扩展了MySQL的能力边界：JSON类型打破了关系型数据库的结构化限制，窗口函数则让复杂的统计分析变得简单高效。

一、JSON类型（MySQL 5.7+）

关系型数据库的结构化存储虽保证了数据规范性，但缺乏灵活性——必须事先设计所有列和数据类型，业务变化时修改表结构成本高。MySQL 5.7引入JSON数据类型（MySQL 8.0解决了JSON日志性能瓶颈），兼顾了关系型数据库的稳定性和非关系型数据库的灵活性。

1. JSON类型的两种形式

JSON类型主要分为JSON对象和JSON数组，满足不同场景的灵活存储需求：

1. 1. JSON 对象

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

1. 2. JSON 数组

[1,2,3]

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

2. 典型应用场景1：多方式登录信息存储

哪些地方需要用到JSON类型呢？举一个简单的例子，现在很多产品的用户登录都支持多种方式，例如手机号、微信、QQ、新浪微博等，但是一般情况下我们又不会要求用户提供所有的这些信息，那么用传统的设计方式，就需要设计多个列来对应多种登录方式，可能还需要允许这些列存在空值，这显然不是很好的选择；另一方面，如果产品又增加了一种登录方式，那么就必然要修改之前的表结构，这就更让人痛苦了。但是，有了 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"}');

如果要查询用户的手机和微信号，可以用如下所示的 SQL 语句。

SELECT `user_id`     , JSON_UNQUOTE(JSON_EXTRACT(`login_info`, '$.tel')) AS 手机号     , JSON_UNQUOTE(JSON_EXTRACT(`login_info`, '$.wechat')) AS 微信 FROM `tb_test`;

+---------+-------------+-----------+| user_id | 手机号      | 微信       |+---------+-------------+-----------+|       1 | 13122335566 | jackfrued ||       2 | 13599876543 | NULL      |+---------+-------------+-----------+

因为支持 JSON 类型，MySQL 也提供了配套的处理 JSON 数据的函数，就像上面用到的json_extract和json_unquote。当然，上面的 SQL 还有更为便捷的写法，如下所示。

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

再举个例子，如果我们的产品要实现用户画像功能（给用户打标签），然后基于用户画像给用户推荐平台的服务或消费品之类的东西，我们也可以使用 JSON 类型来保存用户画像数据，示意代码如下所示。

创建画像标签表。

CREATE TABLE `tb_tags`(`tag_id`   int unsigned NOT NULL COMMENT '标签ID',`tag_name` varchar(20)  NOT NULL COMMENT '标签名',PRIMARY KEY (`tag_id`));INSERT INTO `tb_tags` (`tag_id`, `tag_name`) VALUES    (1, '70后'),    (2, '80后'),    (3, '90后'),    (4, '00后'),    (5, '爱运动'),    (6, '高学历'),    (7, '小资'),    (8, '有房'),    (9, '有车'),    (10, '爱看电影'),    (11, '爱网购'),    (12, '常点外卖');

为用户打标签。

CREATE TABLE `tb_users_tags`(`user_id`   bigint unsigned NOT NULL COMMENT '用户ID',`user_tags` json            NOT NULL COMMENT '用户标签');INSERT INTO `tb_users_tags`VALUES    (1, '[2, 6, 8, 10]'),    (2, '[3, 10, 12]'),    (3, '[3, 8, 9, 11]');

接下来，我们通过一组查询来了解 JSON 类型的巧妙之处。

1. 1. 查询爱看电影（有10这个标签）的用户ID。

   SELECT `user_id`FROM `tb_users_tags`WHERE10MEMBEROF (`user_tags`->'$');

2. 2. 查询爱看电影（有10这个标签）的80后（有2这个标签）用户ID。

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

3. 3. 查询爱看电影或80后或90后的用户ID。

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

说明：上面的查询用到了member of谓词和两个 JSON 函数，json_contains可以检查 JSON 数组是否包含了指定的元素，而json_overlaps可以检查 JSON 数组是否与指定的数组有重叠部分。

二、窗口函数

MySQL 8.0 开始支持窗口函数（也叫 OLAP 函数），专为统计分析设计，弥补了传统聚合函数的不足 —— 聚合函数将多条记录聚合为一条，而窗口函数为每条记录计算结果，记录条数不变。

1. 窗口函数核心概念

• • 窗口：满足特定条件的记录集合（可理解为 “动态的行集合”）；
• • 窗口函数：在每个窗口内对每条记录执行计算，结果保留原记录行数。

2. 基本语法

<窗口函数>OVER (PARTITIONBY<用于分组的列名>ORDERBY<用于排序的列名>ROWSBETWEEN ... AND ...)<窗口函数>OVER (PARTITIONBY<用于分组的列名>ORDERBY<用于排序的列名>RANGEBETWEEN ... AND ...)

上面语法中，窗口函数的位置可以放以下两种函数：

1. 1. 专用窗口函数，包括：lead、lag、first_value、last_value、rank、dense_rank和row_number等。
2. 2. 聚合函数，包括：sum、avg、max、min和count等。

3. 典型应用示例（基于 hrs 数据库）

以下示例基于 hrs 数据库的 tb_emp（员工表）和 tb_dept（部门表），对比 MySQL 8.0 前后的实现方式，体现窗口函数的优势。

例子1：查询按月薪从高到低排在第4到第6名的员工的姓名和月薪。

SELECT*FROM (SELECT `ename`             , `sal`             , ROW_NUMBER() over (ORDERBY `sal` DESC) AS `rk`FROM `tb_emp`) AS `temp`WHERE `rk` between4and6;

说明：上面使用的函数row_number()可以为每条记录生成一个行号，在实际工作中可以根据需要将其替换为rank()或dense_rank()函数，三者的区别可以参考官方文档或阅读《通俗易懂的学会：SQL窗口函数》进行了解。在MySQL 8以前的版本，我们可以通过下面的方式来完成类似的操作。

select `rank`, `ename`, `sal` from (select@a:=@a+1as `rank`, `ename`, `sal` from `tb_emp`, (select@a:=0) as t1 orderby `sal` desc) as `temp` where `rank` between4and6;

例子2：查询每个部门月薪最高的两名的员工的姓名和部门名称。

select `ename`, `sal`, `dname` from (select        `ename`, `sal`, `dno`,rank() over (partitionby `dno` orderby `sal` desc) as `rank`from `tb_emp`) as `temp` naturaljoin `tb_dept` where `rank`<=2;

说明：在MySQL 8以前的版本，我们可以通过下面的方式来完成类似的操作。

select `ename`, `sal`, `dname` from `tb_emp` as `t1` 

natural join tb_deptwhere (select count(*) from tb_emp as t2where t1.dno=t2.dno and t2.sal>t1.sal)<2 order by dno asc, sal desc;

总结

• • JSON 类型：MySQL 5.7+ 引入，支持灵活的非结构化数据存储，搭配JSON_EXTRACT、JSON_CONTAINS等函数可实现精准查询，适合登录信息、用户标签等灵活度高的场景；
• • 窗口函数：MySQL 8.0+ 引入，专为统计分析设计，通过OVER()子句定义窗口，支持分组排名、TopN 查询等复杂操作，相比传统方式更简洁、高效；
• • 两大特性互补：JSON 类型解决 “存储灵活” 问题，窗口函数解决 “分析高效” 问题，是 MySQL 从 “基础存储” 向 “高级分析” 转型的核心能力。