MySQL json数据类型应用

到目前为止，可能很多人会把 JSON 当作一个很大的字符串类型，从表面上来看，没有错。但本质上，JSON 是一种新的类型，有自己的存储格式，还能在每个对应的字段上创建索引，做特定的优化，这是传统字段无法实现的。

JSON 类型的另一个好处是无须预定义字段，字段可以无限扩展。而传统关系型数据库的列都需预先定义，想要扩展需要执行 ALTER TABLE … ADD COLUMN … 这样比较重的操作。

JSON 类型比较适合存储一些修改较少、相对静态的数据

需要注意是，JSON 类型是从 MySQL 5.7 版本开始支持的功能，而 8.0 版本解决了更新 JSON 的日志性能瓶颈。如果要在生产环境中使用 JSON 数据类型，强烈推荐使用 MySQL 8.0 版本。

测试表users：
Info字段类型json

JSON对象

填充测试数据：{“qq”:”123”,”uid”:25,”username”:”张三”,”phone_number”:”123456”}

查询JSON类型字段info里面的字段：

SELECT `name`,email,JSON_UNQUOTE(JSON_EXTRACT(info, '$.phone_number')) AS phone FROM users;

SELECT `name`,JSON_UNQUOTE(JSON_EXTRACT(info, '$.phone_number')) AS phone,JSON_UNQUOTE(JSON_EXTRACT(info, '$.qq')) AS qq FROM users;

上述SQL所使用的聚合函数
JSON_EXTRACT() : Mysql 根据 Json 中指定的 key 取出对应的 value
JSON_UNQUOTE() : 取消引用 Json 值,返回字符串,如果参数为 null,则返回 null 值

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

SELECT `name`,info->>'$.phone_number' AS phone,info->>'$.qq' AS qq FROM users;

添加索引

当JSON数据量非常大，用户希望对 JSON 数据进行有效检索时，可以利用 MySQL 的 函数索引 功能对 JSON 中的某个字段进行索引。
比如在上面的用户登录示例中，假设用户必须绑定唯一手机号，且希望未来能用手机号码进行用户检索时，可以创建下面的索引：

#创建虚拟列phone
ALTER TABLE users ADD COLUMN phone VARCHAR(11) AS (info->>'$.phone_number');
#对虚拟列phone添加唯一索引idx_phone
ALTER TABLE users ADD UNIQUE INDEX idx_phone(phone);

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

EXPLAIN SELECT * FROM users WHERE phone = '18319157642';

修改JSON字段info的phone_number值时，虚拟列phone的值也会同时改变。

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

CREATE TABLE users (
	id UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(64),
	email VARCHAR(64),
	info JSON DEFAULT NULL,
	phone VARCHAR(11) AS (info->>'$.phone_number'),
	PRIMARY KEY(id),
	UNIQUE KEY idx_phone(phone)
) ENGINE=InnoDB DEFAULT CHARSET=uft8mb4 COLLATE=utf8mb4_general_ci

JSON数组

标签表tags：

CREATE TABLE `tags` (
  `tagId` bigint(20) NOT NULL AUTO_INCREMENT,
  `tagName` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`tagId`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

用户标签表usertag：

CREATE TABLE `usertag` (
  `userId` bigint(20) NOT NULL,
  `userTags` json DEFAULT NULL,
  PRIMARY KEY (`userId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

字段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->'$');

SQL分析：

EXPLAIN SELECT * FROM usertag WHERE 10 MEMBER OF (userTags->'$');

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

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

SQL分析：

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

如果想要查询画像为 80 后、90 后，且常看电影的用户，则可以使用函数 JSON_OVERLAP：2或3或10

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

SQL分析：

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

总结：

JSON 类型是 MySQL 5.7 版本新增的数据类型，用好JSON数据类型可以有效解决很多业务中实际问题

1、使用 JSON 数据类型，推荐用 MySQL 8.0.17 以上的版本，性能更好，同时也支持 Multi-Valued Indexes；

2、JSON 数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；

3、不要将有明显关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；

4、JSON 数据类型推荐使用在不经常更新的静态数据存储。

原文：https://mp.weixin.qq.com/s/QIWb02jlGzScky-MhVYPFQ