MySQL学习之JSON类型

前言

我们一定经历过项目随着新版本的迭代而修改表结构的痛苦。关系型数据库是要求我们把当前业务映射为标语表之间的映射，但是随着业务的迭代更新，增减字段就不可避免了。所以，业界推出了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 国际许可协议进行许可