关系型数据库的结构化存储存在一定的弊端，因为它需要预先定义好所有的列以及列对应的类型。但是业务在发展过程中，或许需要扩展单个列的描述功能，这时，如果能用好JSON数据类型，那就能打通关系型和非关系型数据的存储之间的界限，为业务提供更好的架构选择。请不要将JSON类型简单的理解成字符串类型。
JSON数据类型
JSON(JavaScript Object Notation)主要用于互联网应用之间的数据交换。MySQL支持RFC 7195定义的JSON规范，主要有JSON对象和JSON数组两种类型。
JSON类型时从MySQL5.7版本开始支持的功能，而8.0版本解决了更新JSON的日志性能瓶颈问题、如果要在生产环境中使用JSON数据类型，强烈推荐MySQL 8.0 版本。
- JSON是一种新的类型，有自己的存储格式，还能再每个对应的字段上创建索引，做特定的优化，这是传统字符串无法实现的
- JSON无须预定义字段，字段可以无限扩展。而传统关系型数据库的列都需要预先定义，扩展需要修改表结构，执行ALTER TABLE ... ADD COLUMN ...比较重的操作。
业务表结构设计实战
用户登录设计
在数据库中，JSON类型比较适合存储一些修改较少、相对静态的数据，比如用户登录信息的存储：
CREATE TABLE UserLogin (
`userId` BIGINT NOT NULL,
`loginInfo` JSON,
PRIMARY KEY(`userId`)
);
-- 由于当前业务的登录方式越来越多样化，如同一账号支持手机、微信、QQ账号登录，所以可以用JSON类型存储登录的信息。
SET @a = '
{
"cellphone" : "17777777777",
"wxchat" : "MySQL",
"QQ" : "777777777"
}
';
INSERT INTO UserLogin VALUES (1,@a);
-- 插入的用户1，登录有3中方式：手机、微信、QQ
SET @b = '
{
"cellphone" : "18888888888"
}
';
INSERT INTO UserLogin VALUES (2,@b);
-- 插入的用户2，只有手机登录这一种方式
如果不采用JSON数据类型，就要用下面的方式建表：
CREATE TABLE UserLogin (
`userId` BIGINT NOT NULL,
`cellphone` VARCHAR(255),
`wechat` VARCHAR(255),
`QQ` VARCHAT(255),
PRIMARY KEY(`userId`)
);
虽然用传统关系型的方式也可以完成相关数据的存储，但存在两个问题：
- 有些列可能比较稀疏，一些列可能大部分都是NULL值；
- 如果要新增一种登录类型，就需要添加一个新列，而JSON无此烦恼。
- MySQL配套提供了丰富的JSON字段处理函数，用于方便的操作JSON数据
最常见的函数就是JSON_ECTRACT(),用来从JSON数据中提取所需要的字段内容，MySQL还提供了->>表达式，省去每次都写JSON_UNQUOTE、JSON_EXTRACT函数的麻烦
SELECT
userId,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,'$.cellphone')) cellphone,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,'$.wechat')) wechat
FROM UserLogin;
SELECT
userId,
loginInfo->>"$.cellphone" cellphone,
loginInfo->>"$.wechat" wechat
FROM UserLogin;
+--------+-------------+--------+
| userId | cellphone | wechat |
+--------+-------------+--------+
| 1 | 17777777777 | MySQL |
| 2 | 18888888888 | NULL |
+--------+-------------+--------+
2 rows in set (0.01 sec)
-- 以上两条SQL效果是完全一样的
当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索引：
mysql> EXPLAIN SELECT * FROM UserLogin WHERE cellphone = '177777777777';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)
当然，我们可以再一开始创建表的时候，就完成虚拟列集函数索引的创建：
CREATE TABLE UserLogin (
`userId` BIGINT NOT NULL,
`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 | 爱外卖 |
+-------+--------------+
可以看到，表Tags是一张画像定义表，用于描述当前定义有多少个标签，接着给每个用户打标签，比如用户David，他的标签是80后、高学历、小资、有房、常看电影；用户Tom，90后、常看电影、爱外卖。
如果不使用JSON数据类型存储，通过会将用户标签通过字符串，加上分割符的方式，在一个字段中存取用户所有的标签：
+-------+---------------------------------------+
|用户 |标签 |
+-------+---------------------------------------+
|David |80后 ； 高学历 ； 小资 ； 有房 ；常看电影 |
|Tom |90后 ；常看电影 ； 爱外卖 |
+-------+---------------------------------------+
这样做的缺点是：
- 不好搜索特定画像的用户，
- 分隔符也是一种自我约束，在数据库中其实可以任意存储其他数据，最终产生脏数据。
用JSON数据类型就能很好解决这个问题：
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]');
其中userTags存储的标签就是表Tags已定义的那些标签值，只是使用JSON数组类型进行存储。
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：
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：
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：
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)
总结
JSON类型是MySQL5.7版本新增的数据类型，用好JSON数据类型可以有效解决很多业务中实际问题。
- 使用JSON数据类型，推荐使用MySQL 8.0.17以上的版本，性能更好，同时也支持Multi-Valued Indexes；
- JSON数据类型的好处是无须预先定义列，数据本身就有很好的自描述性；
- 不要将有明显关系型的数据用JSON存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；
- JSON数据类型推荐使用在不经常更新的静态数据存储。
网友评论