关于mysql:MySQL之Json类型

1 Json 类型简介

MySQL 5.7 之后提供了 Json 类型，是 MySQL 联合结构化存储和非结构化存储设计进去的一个类型。

在某些场景下，Json 类型几乎是福音。

场景 1: 用户画像，形容用户的标签等相似场景，比方互联网医院类零碎的患者衰弱档案，有很多信息不是必填项，如：身高、体重、三围等等信息，能够应用 Json 存储。

场景 2: 游戏类场景；

场景 3: 存储图片等从属信息，比方图片的分辨率，图片题目等。

2 让咱们看看 Json 怎么用的

创立表，并插入数据

CREATE TABLE UserLogin (
    userId BIGINT NOT NULL,
    loginInfo JSON,
    PRIMARY KEY(userId)
);
INSERT INTO `UserLogin`(`userId`, `loginInfo`) VALUES (1, '{\"QQ\": \"82946772\", \"wxchat\": \" 破产码农 \", \"cellphone\": \"13918888888\"}');
INSERT INTO `UserLogin`(`userId`, `loginInfo`) VALUES (2, '{\"cellphone\": \"15026888888\"}');
INSERT INTO `UserLogin`(`userId`, `loginInfo`) VALUES (3, '{\"QQ\": \"82946772\", \"wxchat\": \" 破产码农 \", \"cellphone\": \"13918888889\"}');

2.1 JSON_EXTRACT 函数，获取 Json 字段中特定属性的值

SELECT JSON_UNQUOTE(JSON_EXTRACT(loginInfo, "$.cellphone")) from UserLogin;

获取 cellphone 属性的值。能够应用 -> 写法或者 ->> 写法。

-- 带引号
SELECT loginInfo->"$.cellphone" from UserLogin;
-- 不带引号
SELECT loginInfo->>"$.cellphone" from UserLogin;

函数阐明：

JSON_EXTRACT(或 ->) 从 JSON 文档返回数据。

JSON_UNQUOTE 勾销援用 JSON 值，并以 utf8mb4 字符串的模式返回后果。

2.2 JSON_CONTAINS 查问 Json 中满足 cellphone 等于 13918888888 的记录

SELECT * from UserLogin where JSON_CONTAINS(loginInfo, '"13918888888"', '$.cellphone')

阐明：应用 JSON_CONTAINS 搜寻指定键的值是否与指定值匹配。

2.3 给 Json 中的字段增加索引

-- 减少虚构列 -cellphone，值通过 loginInfo 计算而来
alter table UserLogin add COLUMN cellphone varchar(50) as (loginInfo->>"$.cellphone");
-- 给 cellphone 这一列减少惟一索引
alter table UserLogin add unique index idex_cellphone(cellphone);

能够看到确实应用了该索引进行查问

2.4 JSON_CONTAINS_PATH 判断 Json 中是否有对应字段

所有记录中有多少记录蕴含 wxchat 字段

SELECT count(*), JSON_CONTAINS_PATH(loginInfo, 'one', '$.wxchat') cp FROM UserLogin GROUP BY cp

返回

阐明，蕴含 wxchat 字段有两条记录，不蕴含的有一条记录。

2.5 JSON_PRETTY 让 Json 长的更好看

SELECT JSON_PRETTY(loginInfo) from UserLogin 

能够返回格式化的 json 数据

{
  "QQ": "82946772",
  "wxchat": "破产码农",
  "cellphone": "13918888888"
}

2.6 JSON_STORAGE_SIZE 返回二进制示意的字节数

返回 loginInfo 字段中存储的二进制示意的字节数。

SELECT max(JSON_STORAGE_SIZE(loginInfo)) FROM UserLogin;
SELECT avg(JSON_STORAGE_SIZE(loginInfo)) FROM UserLogin;
SELECT min(JSON_STORAGE_SIZE(loginInfo)) FROM UserLogin;

2.7 其余函数

• JSON_OBJECT 计算键值对列表并返回蕴含这些键值对的 JSON 对象，应用 JSON_OBJECT。
• JSON_OBJECTAGG 承受两个列名或表达式，并返回一个蕴含 JSON_OBJECTAGG 键值对的 JSON 对象。
• JSON_ARRAY 计算一个值列表，并应用 JSON_ARRAY 返回蕴含这些值的 JSON 数组。
• JSON_ARRAYAGG 将后果会聚合为单个 JSON 数组，其元素由带有 JSON_ARRAYAGG 的行组成。
• JSON_TABLE 从 JSON 文档中提取数据，并将其作为具备 JSON_TABLE 指定列的关系表返回。

总结

JSON 类型是 MySQL 5.7 版本新增的数据类型，用好 JSON 数据类型能够无效解决很多业务中理论问题。最初，我总结下明天的重点内容：应用 JSON 数据类型，举荐用 MySQL 8.0.17 以上的版本，性能更好，同时也反对 Multi-Valued Indexes；

• JSON 数据类型的益处是毋庸事后定义列，数据自身就具备很好的描述性；
• 不要将有显著关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须蕴含的数据；
• JSON 数据类型举荐应用在不常常更新的静态数据存储。

参考：

1、30 mins with MySQL JSON functions：https://dasini.net/blog/2018/…

2、拉钩教育《姜承尧的 MySQL 实战宝典》-04 | 非构造存储：用好 JSON 这张牌：https://kaiwu.lagou.com/cours…