目录
一、简单测试
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
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
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'));
- 1
- 2
- 3
JSON_OBJECT
Json对象:JSON_OBJECT([key, val[, key, val] …])
# 插入JSON对象
insert into log (data) value (JSON_OBJECT('id', 87, 'name', 'carrot'));
- 1
- 2
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]'));
- 1
- 2
- 3
- 4
此时,测试数据有:(还临时添加了几条数据)
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));
- 1
- 2
- 3
# 查询 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','"'));
- 1
- 2
- 3
- 4
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');
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
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') ;
- 1
- 2
- 3
查询 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');
- 1
- 2
- 3
- 4
查看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->‘KaTeX parse error: Expected 'EOF', got '&' at position 166: …oken operator">&̲gt;</span><span….name’ name
from log
where data->‘$.id’ = 87;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
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
- 1
- 2
- 3
- 4
- 5
- 6
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;
- 1
- 2
# 更改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;
- 1
- 2
- 3
- 4
- 5
- 6