title: 68.MySQL的JSON格式支持  
CreateTime: 2021-03-05 17:00:00  
UpdateTime: 2021-03-05 17:00:00  
CategoryName: web  
---

---
title: "68.MySQL的JSON格式支持"
date: 2021-03-05T17:00:00+08:00
draft: false
tags: ["web"]
categories: ["web"]
author: "springrain"
---

## 1. 准备要求  

参考 [MySQL-MGR和主从分离](https://www.jiagou.com/post/05-mysql-mgr/) 安装MySQL 8.0.23 版本,用于JSON测试


```sql

-- 创建表
CREATE TABLE `testjsontable` (
  `id` int NOT NULL,
  `jsonfield` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建测试数据
insert into testjsontable (id, jsonfield) values (1,'{"email":"email","address":[{"line":"abc"},{"name":"456"}]}');
insert into testjsontable (id, jsonfield) values (2,'{"email":"email2","address":[{"line":"abc1"},{"name":"456"}]}');
insert into testjsontable (id, jsonfield) values (3,'{"email":"email","address":[{"line":"abc"},{"name":"123"}]}');
```

## 2. json对象查询
```sql
-- select * from testjsontable  

-- 查询json对象的值 
-- 一般使用JSON_EXTRACT 函数,不使用 ->> 符号,
select * from testjsontable where jsonfield ->> '$.email'='email';
-- 查询json对象的值
select * from testjsontable where JSON_EXTRACT(jsonfield,'$.email') ='email';
```

## 3.json数组查询

需求:查找 jsonfield JSON字段(对象类型)中 address(数组类型) 数组字段中 name 值等于 "456" 的记录

```sql
-- 1.先提取 config JSON 字段中 address 属性,得到数组
SELECT JSON_EXTRACT(jsonfield,'$.address') address FROM testjsontable;
-- 2. 再从 address 数组中查找 name 的值是否等于查找的值,返回 1 或 0,表示是否已找到
SELECT JSON_CONTAINS(JSON_EXTRACT(jsonfield,'$.address'), JSON_OBJECT('name', '456')) 是否已找到 FROM testjsontable;
-- 3.最终语句
select * from testjsontable where JSON_CONTAINS(JSON_EXTRACT(jsonfield,'$.address'), JSON_OBJECT('name', '456')) > 0;
```

## 4.更新json
```sql
-- select * from testjsontable where id=2;

-- 1.JSON_SET() 替换现有值并添加不存在的值.如果大key不存在,JSON_SET无法操作大key的属性,例如 "$.newField.new1","new1" ,如果newField不存在,则无法成功,也不报错,需要使用JSON_MERGE_PATCH函数变通实现.
update testjsontable set jsonfield=JSON_SET(jsonfield,"$.email", "email3","$.newField","new") where id=2;
update testjsontable set jsonfield=JSON_SET(jsonfield,"$.address[0].line", "abc2") where id=2;

-- 2.JSON_INSERT() 只插入新值,但不替换现有值.在这种情况下, $.email 不会被更新,只会添加一个新的字段 $.newemail
update testjsontable set jsonfield=JSON_INSERT(jsonfield,"$.email", "email8","$.newemail","newemail") where id=2;

-- 3.JSON_REPLACE() 只替换现有值,在这种情况下, $.newreplace 不会被添加,只有 $.email 会被更新
update testjsontable set jsonfield=JSON_REPLACE(jsonfield,"$.email", "email9","$.newreplace","newreplace") where id=2;

-- 4.JSON_REMOVE() 从JSON文档中删除数据
update testjsontable set jsonfield=JSON_REMOVE(jsonfield,"$.email") where id=2;

-- 5.JSON_MERGE_PATCH() 合并两个JSON参数,使用后面的值覆盖前面的值,key相同就覆盖,不存在就添加.
update testjsontable set jsonfield=JSON_MERGE_PATCH(jsonfield,'{"email":"9","":""}') where id=2;

-- 6.JSON_CONTAINS_PATH() 查询指定Key是否存在,存在返回1,不存在返回0
select * from testjsontable where  JSON_CONTAINS_PATH(jsonfield,'one','$."email"')>0;


```