Rubin's Blog

  • 首页
  • 关于作者
  • 隐私政策
享受恬静与美好~~~
分享生活的点点滴滴~~~
  1. 首页
  2. MySQL
  3. 正文

MySQL学习之JSON类型

2021年 5月 7日 817点热度 1人点赞 0条评论

前言

我们一定经历过项目随着新版本的迭代而修改表结构的痛苦。关系型数据库是要求我们把当前业务映射为标语表之间的映射,但是随着业务的迭代更新,增减字段就不可避免了。所以,业界推出了NoSQL的数据库来存储结构不固定的数据。

但是在我们的MySQL中,是支持JSON类型的数据存储的。用好这个类型,一样可以解决我们结构不固定数据所带来的问题的。

JSON数据格式的概念以及注意事项

我们可能会把JSON当作大字符串来处理,道理上这是可以的。但是,JSON是有自己的存储结构的。而且,还能在每个对应的字段上创建索引,做特定的优化,这是传统字段串无法实现的。下面是两个简单的JSON结构(对象和对象集合)示例:

{
    "Image": {
       "Width": 800,
       "Height": 600,
       "Title": "View from 15th Floor",
       "Thumbnail": {
            "Url": "http://www.example.com/image/481989943",
            "Height": 125,
            "Width": 100
        },
       "IDs": [116, 943, 234, 38793]
     }
}
[
   {
     "precision": "zip",
     "Latitude": 37.7668,
     "Longitude": -122.3959,
     "Address": "",
     "City": "SAN FRANCISCO",
     "State": "CA",
     "Zip": "94107",
     "Country": "US"
   },
   {
     "precision": "zip",
     "Latitude": 37.371991,
     "Longitude": -122.026020,
     "Address": "",
     "City": "SUNNYVALE",
     "State": "CA",
     "Zip": "94085",
     "Country": "US"
   }
 ]

需要注意是,JSON类型是从 MySQL 5.7 版本开始支持的功能,而 8.0 版本解决了更新JSON的日志性能瓶颈。如果要在生产环境中使用JSON数据类型,强烈推荐使用 MySQL 8.0 版本。此类型的适用字段是那些结构不固定但是变更很少的字段(比如用户的登陆方式,用户的标签集合等等)。

业务表设计实战

用户登录方式

我们现在的网站,支持多种登陆方式已经很普遍。但是在我们的网站迭代过程中,从一种到支持多种是有一个过程的。如果使用关系型数据库的传统设计,我们将加一种登陆方式而添加一个字段,这样的话,我们会频繁修改表结构。这显然不是一种很好的方案。

基于上面这种情况,我们用JSON类型来存储用户的登陆方式是一个很好的选择。示例表结构设计如下:

DROP TABLE IF EXISTS UserLogin;
CREATE TABLE UserLogin (
    userId BIGINT NOT NULL,
    loginInfo JSON,
    PRIMARY KEY(userId)
);

比如我们现在支持手机、微信、QQ 账号登录,那么我们的账户号信息就可以存入以下数据:

SET @a = '
{
    "cellphone" : "13918888888",
    "wxchat" : "破产码农",
    "QQ" : "82946772"
}
';

INSERT INTO UserLogin VALUES (1, @a);

SET @b = '
{
    "cellphone" : "15026888888"
}
';

INSERT INTO UserLogin VALUES (2, @b);

我们可以看出JSON格式的数据是很灵活的,我们支持哪种方式,就添加哪种方式,而不用修改表结构。不支持的方式也不用存储NULL值,也避免了NULL值的空间浪费。

因为支持了新的JSON类型,MySQL配套提供了丰富的JSON字段处理函数,用于方便地操作JSON数据,具体可以见 MySQL官方文档。

其中,最常见的就是函数JSON_EXTRACT,它用来从 JSON数据中提取所需要的字段内容,如下面的这条SQL语句就查询用户的手机和微信信息。

SELECT
    userId,
    JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,
    JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchat
FROM UserLogin;

+--------+-------------+--------------+

| userId | cellphone   | wxchat       |

+--------+-------------+--------------+

|      1 | 13918888888 | 破产码农     |

|      2 | 15026888888 | NULL         |

+--------+-------------+--------------+

2 rows in set (0.01 sec)

当然了,每次写 JSON_EXTRACT、JSON_UNQUOTE 非常麻烦,MySQL还提供了 ->> 表达式,和上述 SQL 效果完全一样:

SELECT 
    userId,
    loginInfo->>"$.cellphone" cellphone,
    loginInfo->>"$.wxchat" wxchat
FROM UserLogin;

当JSON数据量非常大,用户希望对JSON数据进行有效检索时,可以利用MySQL的函数索引功能对JSON中的某个字段进行索引。

比如在上面的用户登录示例中,假设用户必须绑定唯一手机号,且希望未来能用手机号码进行用户检索时,可以创建下面的索引:

ALTER TABLE UserLogin ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone");
ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone);

上述 SQL 首先创建了一个虚拟列cellphone,这个列是由函数 loginInfo->>"$.cellphone"计算得到的。然后在这个虚拟列上创建一个唯一索引idx_cellphone。这时再通过虚拟列cellphone 进行查询,就可以看到优化器会使用到新创建的 idx_cellphone索引:

EXPLAIN SELECT  *  FROM UserLogin WHERE cellphone = '13918888888'\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: UserLogin

   partitions: NULL

         type: const

possible_keys: idx_cellphone

          key: idx_cellphone

      key_len: 1023

          ref: const

         rows: 1

     filtered: 100.00

        Extra: NULL

1 row in set, 1 warning (0.00 sec)

当然,我们可以在一开始创建表的时候,就完成虚拟列及函数索引的创建。如下表创建的列cellphone对应的就是 JSON中的内容,是个虚拟列;uk_idx_cellphone就是在虚拟列 cellphone上所创建的索引。

CREATE TABLE UserLogin (
    userId BIGINT,
    loginInfo JSON,
    cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
    PRIMARY KEY(userId),
    UNIQUE KEY uk_idx_cellphone(cellphone)
);

用户画像

某些业务需要做用户画像(也就是对用户打标签),然后根据用户的标签,通过数据挖掘技术,进行相应的产品推荐。比如:

  • 在电商行业中,根据用户的穿搭喜好,推荐相应的商品。
  • 在音乐行业中,根据用户喜欢的音乐风格和常听的歌手,推荐相应的歌曲。
  • 在金融行业,根据用户的风险喜好和投资经验,推荐相应的理财产品。

在这,强烈推荐使用 JSON类型在数据库中存储用户画像信息,并结合 JSON数组类型和多值索引的特点进行高效查询。假设有张画像定义表:

CREATE TABLE Tags (
    tagId bigint auto_increment,
    tagName varchar(255) NOT NULL,
    primary key(tagId)
);

SELECT * FROM Tags;

+-------+--------------+

| tagId | tagName      |

+-------+--------------+

|     1 | 70后         |

|     2 | 80后         |

|     3 | 90后         |

|     4 | 00后         |

|     5 | 爱运动       |

|     6 | 高学历       |

|     7 | 小资         |

|     8 | 有房         |

|     9 | 有车         |

|    10 | 常看电影     |

|    11 | 爱网购       |

|    12 | 爱外卖       |

+-------+--------------+

我们的用户画像表设计如下:

DROP TABLE IF EXISTS UserTag;
CREATE TABLE UserTag (
    userId bigint NOT NULL,
    userTags JSON,
    PRIMARY KEY (userId)
);

INSERT INTO UserTag VALUES (1,'[2,6,8,10]');
INSERT INTO UserTag VALUES (2,'[3,10,12]');

MySQL 8.0.17 版本开始支持 Multi-Valued Indexes,用于在 JSON数组上创建索引,并通过函数 MEMBER OF、JSON_CONTAINS、JSON_OVERLAPS 来快速检索索引数据。所以你可以在表 UserTag上创建 Multi-Valued Indexes:

ALTER TABLE UserTag ADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array)));

如果想要查询用户画像为常看电影的用户,可以使用函数 MEMBER OF:

EXPLAIN SELECT * FROM UserTag WHERE 10 MEMBER OF(userTags->"$")\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: UserTag

   partitions: NULL

         type: ref

possible_keys: idx_user_tags

          key: idx_user_tags

      key_len: 9

          ref: const

         rows: 1

     filtered: 100.00

        Extra: Using where

1 row in set, 1 warning (0.00 sec)

SELECT * FROM UserTag WHERE 10 MEMBER OF(userTags->"$");

+--------+---------------+

| userId | userTags      |

+--------+---------------+

|      1 | [2, 6, 8, 10] |

|      2 | [3, 10, 12]   |

+--------+---------------+

2 rows in set (0.00 sec)

如果想要查询画像为 80 后,且常看电影的用户,可以使用函数 JSON_CONTAINS:

EXPLAIN SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->"$", '[2,10]')\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: UserTag

   partitions: NULL

         type: range

possible_keys: idx_user_tags

          key: idx_user_tags

      key_len: 9

          ref: NULL

         rows: 3

     filtered: 100.00

        Extra: Using where

1 row in set, 1 warning (0.00 sec)

SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->"$", '[2,10]');

+--------+---------------+

| userId | userTags      |

+--------+---------------+

|      1 | [2, 6, 8, 10] |

+--------+---------------+

1 row in set (0.00 sec)

如果想要查询画像为 80 后、90 后,且常看电影的用户,则可以使用函数 JSON_OVERLAP:

EXPLAIN SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]')\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: UserTag

   partitions: NULL

         type: range

possible_keys: idx_user_tags

          key: idx_user_tags

      key_len: 9

          ref: NULL

         rows: 4

     filtered: 100.00

        Extra: Using where

1 row in set, 1 warning (0.00 sec)

SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]');

+--------+---------------+

| userId | userTags      |

+--------+---------------+

|      1 | [2, 6, 8, 10] |

|      2 | [3, 10, 12]   |

+--------+---------------+

2 rows in set (0.01 sec)

具体的api有很多,api官方文档:https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html

本作品采用 知识共享署名 4.0 国际许可协议 进行许可
标签: MySQL
最后更新:2022年 6月 9日

RubinChu

一个快乐的小逗比~~~

打赏 点赞
< 上一篇
下一篇 >

文章评论

razz evil exclaim smile redface biggrin eek confused idea lol mad twisted rolleyes wink cool arrow neutral cry mrgreen drooling persevering
取消回复
文章目录
  • 前言
  • JSON数据格式的概念以及注意事项
  • 业务表设计实战
    • 用户登录方式
  • 用户画像
最新 热点 随机
最新 热点 随机
问题记录之Chrome设置屏蔽Https禁止调用Http行为 问题记录之Mac设置软链接 问题记录之JDK8连接MySQL数据库失败 面试系列之自我介绍 面试总结 算法思维
Linux之时间同步 java并发编程之并发容器 加速本机对github的访问速度 RocketMQ之源码环境搭建 SpringBoot之源码环境搭建 MHA高可用架构搭建

COPYRIGHT © 2021 rubinchu.com. ALL RIGHTS RESERVED.

Theme Kratos Made By Seaton Jiang

京ICP备19039146号-1