MySQL 进阶#

#MySQL 高阶特性实战

在熟练掌握了增删改查之后，想让 SQL 查询既高效又易于维护，往往需要借助 MySQL 提供的一些高阶特性。它们既能解决传统relational-database在面对半结构化数据时的尴尬，又能优雅地实现复杂的排序和聚合分析。

本文选取两个最常用的特性深入讲解：JSON 类型（灵活存储半结构化数据）和窗口函数（高效处理分析型查询）。

#JSON 类型：在relational-database里使用文档模型

很多开发者都曾吐槽：relational-database的“表格”太死板，所有列必须提前定义好类型，业务需求一变化就得 ALTER TABLE，维护成本不小。

从 MySQL 5.7 开始，官方引入了原生的 JSON 数据类型；到 MySQL 8.0，又解决了 JSON 字段的日志性能瓶颈。可以说，JSON 类型相当于在 MySQL 里内置了一个轻量级的文档数据库，让数据持久化变得更加灵活。

JSON 类型主要包含两种结构：

1. JSON 对象（类似 Python 字典）

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

2. JSON 数组

   [1, 2, 3]

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

下面通过两个经典场景，看看 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"}');

查询用户的手机号和微信号时，可以使用 JSON_EXTRACT 提取值，再通过 JSON_UNQUOTE 去掉引号。MySQL 还提供了 ->> 语法糖，写起来更简洁：

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

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

结果如下：

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

#场景二：用户画像与标签系统

另一个绝佳的应用是用户画像。我们可以把多个标签 ID 存为 JSON 数组，从而避免繁琐的“用户-标签”中间表，查询也非常灵活。

1. 创建标签字典表：

   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, '常点外卖');

2. 用户标签关联表（使用 JSON 数组）：

   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, 8, 9, 11]');

3. 常用查询技巧：

   • 查询包含特定标签的用户（例如标签 10，爱看电影）：

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

   • 查询同时包含多个标签的用户（爱看电影 10 且是 80 后 2）：

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

   • 查询包含任一标签的用户（爱看电影 10 或是 80 后 / 90 后 2, 3）：

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

提示：MEMBER OF、JSON_CONTAINS 和 JSON_OVERLAPS 都是 MySQL 8.0 针对 JSON 数组提供的内置函数，可以大幅简化标签类查询。

#窗口函数：数据分析利器

MySQL 从 8.0 版本开始全面支持窗口函数（Window Functions）。在此之前，类似功能只有在 Oracle、PostgreSQL 等数据库中才能使用，通常被称为 OLAP（联机分析处理）函数。

#什么是“窗口”？

“窗口”可以理解成满足特定条件的记录集合。窗口函数就是在这个集合上执行的特殊函数。它与普通聚合函数最大的区别在于：聚合函数会把多条记录合并成一条，而窗口函数会让每条记录都保留，并在此基础上附加计算结果。

#基本语法

窗口函数的语法中，OVER 关键字用于定义窗口的范围：

<窗口函数> OVER (
    PARTITION BY <分组列>
    ORDER BY <排序列>
    ROWS/RANGE BETWEEN <窗口帧范围>
)

窗口函数主要分为两类：

1. 专用窗口函数：ROW_NUMBER()、RANK()、DENSE_RANK()、LEAD()、LAG() 等。
2. 聚合函数作为窗口函数：SUM()、AVG()、MAX()、MIN()、COUNT() 等。

下面以经典的 hrs 数据库（员工/部门表）为例，演示两个典型场景。

#示例一：全局排序与分页取值

需求：查询月薪从高到低排在第 4 到第 6 名的员工。

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

根据业务需要，可以灵活选择排序函数：

• ROW_NUMBER()：连续不重复排序（1, 2, 3, 4, ...）
• RANK()：并列跳跃排序（1, 2, 2, 4, ...）
• DENSE_RANK()：并列连续排序（1, 2, 2, 3, ...）

在 MySQL 8.0 之前，实现同样的功能通常要靠变量模拟，代码晦涩且性能一般：

-- 旧版本写法（不推荐）
SELECT `rank`, `ename`, `sal` 
FROM (
       SELECT @a:=@a+1 AS `rank`, `ename`, `sal` 
       FROM `tb_emp`, (SELECT @a:=0) AS t1 
       ORDER BY `sal` DESC
) AS `temp` 
WHERE `rank` BETWEEN 4 AND 6;

#示例二：分组内排序

需求：查询每个部门月薪最高的两名员工。

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

这里的核心是 PARTITION BY，它会按部门编号把数据分成多个独立的窗口，然后在每个窗口内单独排序。最终再通过 WHERE rank <= 2 筛选出每个部门的 Top 2。

#小结

• JSON 类型 让 MySQL 具备了处理半结构化数据的能力，特别适合存储动态属性、多渠道信息、用户标签等场景，结合内置函数可以高效查询。
• 窗口函数 则大大简化了排名、分组排序、累计统计等分析型查询，代码清晰，性能也优于传统变量或子查询方案。

这两种特性都是 MySQL 8.0 时代的必备技能，善加利用可以显著提升开发效率与查询性能。