开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL的JSON 数据类型 -> 正文阅读

[大数据]MySQL的JSON 数据类型

一、简单测试

1. 创建测试数据库

MySQL数据类型详解：https://dev.mysql.com/doc/refman/8.0/en/json.html

MySQLJSON函数：https://dev.mysql.com/doc/refman/8.0/en/json-functions.html

数据库DDL语句：

-- auto-generated definition
create table log
(
    id   int auto_increment
        primary key,
    data text null
);

2. JSON增删改查语句

1. 新增

JSON_ARRAY

Json数组：JSON_ARRAY([val[, val] …])

# 插入json数组
insert into log (data) value (JSON_ARRAY('hot'));
insert into log (data) value (JSON_ARRAY('hot','new'));

在这里插入图片描述

JSON_OBJECT

Json对象：JSON_OBJECT([key, val[, key, val] …])

# 插入JSON对象
insert into log (data) value (JSON_OBJECT('id', 87, 'name', 'carrot'));

在这里插入图片描述
Json字符串：JSON_QUOTE(string)

# 插入JSON字符串
insert into log (data) value (JSON_QUOTE('null'));
insert into  log(data) value (JSON_QUOTE('"null"'));
insert into  log(data) value (JSON_QUOTE('[1, 2, 3]'));

在这里插入图片描述
此时，测试数据有：（还临时添加了几条数据）

2. 查询

JSON_CONTAINS

JSON_CONTAINS(target, candidate[, path])

Json数组：

# 查询 data 是否包含 数值1
select  * from log a where JSON_CONTAINS(a.data,'1');
select  * from log a where JSON_CONTAINS(a.data,CONCAT(1));

在这里插入图片描述

# 查询 data 是否包含 字符串hot
select  * from log a where JSON_CONTAINS(a.data,'hot'); # 报错。因为在MySQL中，去除单引号后，hot非法
select  * from log a where JSON_CONTAINS(a.data,'"hot"');
select  * from log a where JSON_CONTAINS(a.data,CONCAT('"','hot','"'));

在这里插入图片描述

Json对象：

# 查询 data里面 key为a 的 value 为1 的列
select  * from log a where JSON_CONTAINS(a.data,'1','$.a');
# 查询 data里面 key为a 的 value 为{"d": 4} 的列
select  * from log a where JSON_CONTAINS(a.data,'{"d": 4}','$.a');
# 查询 data里面 key为c的 value 为 {"d": 4} 的列
select  * from log a where JSON_CONTAINS(a.data,'{"d": 4}','$.c');
# 查询 data里面 key为d 的 value 为 字符串5 的列
select  * from log a where JSON_CONTAINS(a.data,'"5"','$.d');
select  * from log a where JSON_CONTAINS(a.data,CONCAT('"','5','"'),'$.d');

JSON_EXTRACT、column->path

JSON_EXTRACT(json_doc, path[, path] …)

-> 运算符只是简单地提取一个值。

查询 data里面 key为id 的列

# 查询 data里面 key为id 的列
select  * from log a where a.data->'$.id';
select  * from log a where JSON_EXTRACT(a.data,'$.id') ;

在这里插入图片描述
查询 data里面 key为id 的 value为87

# 查询 data里面 key为id 的 value为87
select  * from log a where a.data->'$.id' = 87;
select  * from log a where JSON_EXTRACT(a.data,'$.id') = 87;
# 等价于：select  * from log a where JSON_CONTAINS(a.data,'87','$.id');

在这里插入图片描述
查看date里面key为id，value为87的行，取出Json对象里面的值

# 查看date里面key为id，value为87的行，取出Json对象里面的值
select JSON_EXTRACT(data,'$.id') id
     ,JSON_EXTRACT(data,'$.name') name
from log
where JSON_EXTRACT(data,'$.id' )= 87;

select data->'$.id' id
     ,data->'$.name' name
from log
where data->'$.id' = 87;

在这里插入图片描述

column->>path

这是一个改进的、不带引号的提取操作符。

select  a.data  -> '$.coverImage' as '->'
       ,(a.data  ->> '$.coverImage')  as '->>'
       , concat('http://localhost:15672/',a.data  -> '$.coverImage') as '->Concat'
       , concat('http://localhost:15672/',a.data  ->> '$.coverImage')  as '->>Concat'
from log a
where a.id = 98

在这里插入图片描述

3. 修改

JSON_ARRAY_APPEND

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)

将值附加到 JSON 文档中指定数组的末尾并返回结果。

# 查看当前 99 的 data 信息
select a.data from log a where a.id = 99;

在这里插入图片描述

# 更改99的信息
update log a
set data = JSON_ARRAY_APPEND(a.data, '$[1]', 1)
where a.id = 99;
# 查看更改后的 99 的 data 信息
select a.data from log a where a.id = 99;