MYSQL8 处理JSON 我不再是豆包，我是干粮

最近来了一个项目，本身如果用MONGODB 有点大材小用，所以为了避免某些表继续使用text字段来处理JSON 数据的方式，让技术水平上一个档次，并且公司也不在上MYSQL 5.7 的新项目，全部是8.018这个版本。

继续上一篇文字，那就看看MYSQL 8的野心到底是如何展现的。顺便研究完，给开发一个靠谱的方案，解决人家的问题。（如果是MYSQL5.7 打死我也不会给推荐的）

首先我们的界定为什么要使用MYSQL 8 种的JSON 格式，而不是之前应付的text type。

1 在数据输入的时候，能进行数据的检测，是否符合JSON 的标准

2 数据在处理的时候，通过键值对的方式进行查询，不在需要将字段里面的数据读取后，在进行处理。

用一句话来讲，更规范，更快速，专业的处理JSON，MYSQL OK的

为什么一个传统数据库要开始专注于处理非结构，半结构化得数据，因为需求，需求决定着一切，现在不同系统中传输信息的格式是什么，XML， 明文，OMG， JSON JSON JSON 。如果所有传统数据库都不能处理JSON ，那很可能，由于某些原因，某些业务场景，就不在需要什么 MYSQL ,PG 这样的数据库，取代的就是 MONGODB 。好在 PG 天生就是 JSON 好手， MYSQL 8 的一部分野心也是 JSON， 所以 MONGODB加油呀。

下面是一张，接受其他公司的数据的一张表。

其实MYSQL 5.7 本身也支持JSON ，之前也写过一篇，不过那篇是恶评，太烂了。MYSQL 8 如果使用JSON 也要在 8.014版本以上，否则也会吃亏在数组方面。在MySQL 8.0中，优化器可以执行JSON列的局部就地更新，而不是删除旧文档并将整个新文档写入该列，当然这也是有条件的。

CREATE TABLE `t_clue_info` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`fk_applyid` int(11) NOT NULL COMMENT ''申请id'',

`context` json DEFAULT NULL COMMENT ''接口结果'',

`cdate` datetime NOT NULL COMMENT ''创建时间'',

PRIMARY KEY (`id`),

KEY `ix_fk_applyid` (`fk_applyid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into t_tmall_clue_info (fk_applyid,context,cdate) values (1982873,'{"apply_id":"cm-387237","info":"sim-093823","date":"2020-03-18 02:00:09"}',"2020-03-18 02-09-09");

小结：1 插入的JSON 的字段要必须是 JSON 的格式

2 在插入的时候使用单引号进行包含，里面是正确的JSON格式

我们继续提高点难度，让MYSQL中开始存储数组

insert into t_tmall_clue_info (fk_applyid,context,cdate) values (1982823,'{"apply_id":"cm-387234","info":"sim-093856","tag":[12,34,56],"date":"2020-03-11 09:00:09"}',"2020-03-18 02-09-19");

insert into t_tmall_clue_info (fk_applyid,context,cdate) values (1982823,'{"apply_id":"cm-387234","info":"sim-093856","tag":[12,34,56],"date":"2020-03-11 09:00:09"}',"2020-03-18 02-09-19");

insert into t_tmall_clue_info (fk_applyid,context,cdate) values (1982823,'{"apply_id":"cm-387234","info":"sim-093856","tag":[12,34,56],"date":"2020-03-11 09:00:09","contant":"the informatiton is from car100"}',"2020-03-18 02-09-19");

insert into t_tmall_clue_info (fk_applyid,context,cdate) values (1982823,'{"apply_id":"cm-387234","info":"sim-093856","tag":[12,34,56],"date":"2020-03-11 09:00:09","contant":"the informatiton is from car100","add-in":"re-092878"}',"2020-03-18 02-09-19");

如果你细心的话，就会发现我上面的输入和下面的展示的地方，有点意思的地方，展示的时候和你的输入的字段顺序无太大关系，他会自动将一些类似的东西进行整齐的排列（尽量），我说不上这样做是好还是...... 但看上去，绝对要比使用 text 那样的方式要好的多，至少你一眼就可以看出JSON 里面缺哪个多哪个。

查询的方式也，越来越有点意思了

SELECT * FROM t_tmall_clue_info WHERE context->>'$.info' = 'sim-093856';

MYSQL 8 中的 Multi-Valued Indexes，其实就是为MYSQL JSON 数组而生的。一个多值索引可以有多个索引记录。多值索引用于索引JSON数组。例

怎么在上面的表添加一个多值索。"tag":[12,34,56]

CREATE INDEX idx_tmall_tag ON t_tmall_clue_info ( (CAST(context->'$.tag' AS UNSIGNED ARRAY)) );

SELECT * FROM t_tmall_clue_info WHERE JSON_CONTAINS(context->'$.tag',cast('[78]' AS JSON));

SELECT * FROM t_tmall_clue_info WHERE JSON_OVERLAPS(context->'$.tag',cast('[78]' AS JSON));

Json_contains 和 json_overlaps 之间的区别是，一个包含数组中的值某即可，另一个不行，必须是你查询的数组的值都包含才可以。

在添加完索引，查询数组里面的值，是可以走索引的。

SELECT * FROM t_tmall_clue_info WHERE 78 MEMBER OF(context->'$.tag');

当然只查询数组中的一个值也是可以的。

这里需要注意的几个地方

1 如果多值键部分有一个空数组，则不会向索引中添加任何项，并且索引扫描无法访问数据记录。

2 多值索引是虚拟列上的虚拟索引，所以它们必须遵守与虚拟生成列上的二级索引相同的规则。

下面是一些其他方面的操作

如何只显示一些需要显示的东西，并且可以看到这里里面如果有没有值的情况，MySQL会显示 null

select context-> '$.tag',context-> '$.date' from t_tmall_clue_info;

上面两个语句都可以显示相关的信息，但是符号的不同

->

->>

在展示阶段相当于将""去掉和不去掉的选择，这样的符号的名字叫inline path operator

其实目前我们遇到的情况，大部分的需求都是查询，元数据是不会被修改的，所以UDPATE的事情，就下回再说吧。

另外，即使是在MYSQL中使用非数组信息，要变成数组也是很简单的

SELECT JSON_ARRAY(date_add(now(), interval 1 day), date_add(now(), interval 2 day), date_add(now(), interval 3 day), date_add(now(), interval 4 day), date_add(now(), interval 5 day)) as array1;

当然如果想把一堆值，变成 KEY VALUE 也很简单 json_object 也可以帮助你

由于目前这个项目不大，每天的数据流也还OK， 所以选择 MYSQL 8 JSON 的处理方式，但如果数据量大，并且有复杂的查询 聚合，等等，那就要“大芒果” 来解决问题了。