第 39 课1 天

41.MySQL新特性

很多开发者在使用关系型数据库做数据持久化的时候，常常感到结构化的存储缺乏灵活性，因为必须事先设计好所有的列以及对应的数据类型。在业务发展和变化的过程中，如果需要

课程内容

MySQL新特性

JSON类型

很多开发者在使用关系型数据库做数据持久化的时候，常常感到结构化的存储缺乏灵活性，因为必须事先设计好所有的列以及对应的数据类型。在业务发展和变化的过程中，如果需要修改表结构，这绝对是比较麻烦和难受的事情。从 MySQL 5.7 版本开始，MySQL引入了对 JSON 数据类型的支持（MySQL 8.0 解决了 JSON 的日志性能瓶颈问题），用好 JSON 类型，其实就是打破了关系型数据库和非关系型数据库之间的界限，为数据持久化操作带来了更多的便捷。

JSON 类型主要分为 JSON 对象和 JSON数组两种，如下所示。

JSON 对象

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

JSON 数组

[1, 2, 3]

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

哪些地方需要用到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      |
+---------+-------------+-----------+

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

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]');

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

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

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

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

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

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

<窗口函数> OVER (PARTITION BY <用于分组的列名> ORDER BY <用于排序的列名>  ROWS BETWEEN ... AND ...)
<窗口函数> OVER (PARTITION BY <用于分组的列名> ORDER BY <用于排序的列名> RANGE BETWEEN ... AND ...)

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

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

select `ename`, `sal`, `dname` from `tb_emp` as `t1` 
natural join `tb_dept` 
where (
        select count(*) from `tb_emp` as `t2` 
        where `t1`.`dno`=`t2`.`dno` and `t2`.`sal`>`t1`.`sal` 
)<2 order by `dno` asc, `sal` desc;