MySQL中JSON操作

最新推荐文章于 2024-05-15 10:46:25 发布
最新推荐文章于 2024-05-15 10:46:25 发布
阅读量386 收藏 1
点赞数
文章标签： mysql json 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_50330544/article/details/134326732
版权

MySQL中JSON操作

引言

Mysql5.7版本以后提供了一个原生的Json类型，Json值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（document elements）的内部二进制（internal binary）格式。 在Json列插入或者更新的时候将会自动验证Json文本，未通过验证的文本将产生一个错误信息。 Json文本采用标准的创建方式，可以使用大多数的比较操作符进行比较操作，例如：=, <, <=, >, >=, <>, != 和 <=>。
mybatis中操作json

1.建表及添加数据

--先创建一个简单的含json格式的数据库表，其中json_value就为json格式的字段。
CREATE TABLE `dept` (
  `id` int(11) NOT NULL,
  `dept` varchar(255) DEFAULT NULL,
  `json_value` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--添加数据
insert into dept VALUES(1,'部门1','{"deptName": "部门1", "deptId": "1", "deptLeaderId": "3"}');
insert into dept VALUES(2,'部门2','{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}');
insert into dept VALUES(3,'部门3','{"deptName": "部门3", "deptId": "3", "deptLeaderId": "5"}');
insert into dept VALUES(4,'部门4','{"deptName": "部门4", "deptId": "4", "deptLeaderId": "5"}');
insert into dept VALUES(5,'部门5','{"deptName": "部门5", "deptId": "5", "deptLeaderId": "5"}');

2.基础查询

用法提示：
如果json字符串不是数组，则直接使用$.字段名
如果json字符串是数组[Array]，则直接使用$[对应元素的索引id]

2.1 一般json查询

使用 json字段名->'$.json属性' 进行查询条件

--查询deptLeaderId=5的数据
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';

在这里插入图片描述

2.2 多个条件查询

--查dept为“部门3”和deptLeaderId=5的数据，sql如下：
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and dept='部门3';

2.4 关联表查询

--再创建一张包含json格式的表
CREATE TABLE `dept_leader` (
  `id` int(11) NOT NULL,
  `leaderName` varchar(255) DEFAULT NULL,
  `json_value` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--插入一些测试数据
insert into dept_leader VALUES(1,'leader1','{"name": "王一", "id": "1", "leaderId": "1"}');
insert into dept_leader VALUES(2,'leader2','{"name": "王二", "id": "2", "leaderId": "3"}');
insert into dept_leader VALUES(3,'leader3','{"name": "王三", "id": "3", "leaderId": "4"}');
insert into dept_leader VALUES(4,'leader4','{"name": "王四", "id": "4", "leaderId": "5"}');
insert into dept_leader VALUES(5,'leader5','{"name": "王五", "id": "5", "leaderId": "5"}');
--连表查询在dept表中部门leader在dept_leader中的详情
SELECT * from dept,dept_leader 
WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;

3.JSON函数操作

上述查询的json都是整条json数据，这样看起来不是很方便，那么如果我们只想看json中的某个字段怎么办？

3.1 ->、->>区别

->会保持json文档格式中原来格式，但->>会把所有引号去掉。
特别注意：->当做where查询是要注意类型的，->>是不用注意类型的

select * from dept where json_value->'$.deptId'=1

在这里插入图片描述

select * from dept where json_value->'$.deptId'='1'

在这里插入图片描述

3.2 JSON_EXTRACT 函数：从json中返回想要的字段

1.可以通过key查询value值(如果是json数组类型,可以通过下标获取对应位置的值)，非常方便。

2.使用场景：JSON_EXTRACT性能验证 , 通过查看执行计划，验证全部都是全表扫描。数据量不大json字符串较大则可以采用，数据量较大不建议使用。

3.语法：JSON_EXTRACT(json_doc, path[, path] …)

4.栗子

select 
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.name") as name,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.tel_no") as tel_no,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[0]") as hobby_1,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[1]") as hobby_2,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[2]") as hobby_3,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[3]") as hobby_4;

结果：
在这里插入图片描述
举例查询：

-- 创建测试表
CREATE TABLE `tab_json` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `data` json DEFAULT NULL COMMENT 'json字符串',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 新增数据
-- {"Tel": "132223232444", "name": "david", "address": "Beijing"}
-- {"Tel": "13390989765", "name": "Mike", "address": "Guangzhou"}
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (1, '{\"Tel\": \"132223232444\", \"name\": \"david\", \"address\": \"Beijing\"}');
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (2, '{\"Tel\": \"13390989765\", \"name\": \"Mike\", \"address\": \"Guangzhou\"}');
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (3, '{"success": true,"code": "0","message": "","data": {"name": "jerry","age": "18","sex": "男"}}');
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (4, '{"success": true,"code": "1","message": "","data": {"name": "tome","age": "30","sex": "女"}}');

-- 查询
select * from tab_json;

-- json_extract
select json_extract('{"name":"Zhaim","tel":"13240133388"}',"$.tel");
select json_extract('{"name":"Zhaim","tel":"13240133388"}',"$.name");

-- 对tab_json表使用json_extract函数
select json_extract(data,'$.name') from tab_json;

#如果查询没有的key,那么是可以查询,不过返回的是NULL.
select json_extract(data,'$.name'),json_extract(data,'$.Tel') from tab_json;  
select json_extract(data,'$.name'),json_extract(data,'$.tel') from tab_json;  
select json_extract(data,'$.name'),json_extract(data,'$.address') from tab_json;

-- 条件查询
select json_extract(data,'$.name'),json_extract(data,'$.Tel') from tab_json where json_extract(data,'$.name') = 'Mike';  

-- 嵌套json查询
select * from tab_json where json_extract(data,'$.success') = true;  
select json_extract(data,'$.data') from tab_json where json_extract(data,'$.success') = true;  
-- 查询data对应json中key为name的值
select json_extract( json_extract(data,'$.data'),'$.name') from tab_json where json_extract(data,'$.code') = "1";  
select json_extract( json_extract(data,'$.data'),'$.name'),json_extract( json_extract(data,'$.data'),'$.age') from tab_json where json_extract(data,'$.code') = "0";  

-- 性能验证 , 通过验证全部都是全表扫描，使用场景：数据量不大json字符串较大则可以采用，数据量较大不建议使用。
explain select * from tab_json where json_extract(data,'$.success') = true;  
explain select json_extract(data,'$.data') from tab_json where json_extract(data,'$.success') = true;  
-- 查询data对应json中key为name的值
explain select json_extract( json_extract(data,'$.data'),'$.name') from tab_json where json_extract(data,'$.code') = "1";  
explain select json_extract( json_extract(data,'$.data'),'$.name'),json_extract( json_extract(data,'$.data'),'$.age') from tab_json where json_extract(data,'$.code') = "0";

3.3 JSON_CONTAINS():JSON格式数据是否在字段中包含特定对象

用法: JSON_CONTAINS(target, candidate[, path])

--查询包含deptName=部门5的对象
select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))

3.4 JSON_OBJECT():将一个键值对列表转换成json对象

比如我们想查询某个对象里面的值等于多少
比如我们添加这么一组数据到dept表中：

insert into dept VALUES(6,'部门9','{"deptName": {"dept":"de","depp":"dd"}, "deptId": "5", "deptLeaderId": "5"}');

我们可以看到deptName中还有一个对象，里面还有dept和depp两个属性字段，那么我们应该怎么查询depp=dd的员工呢。

用法：JSON_OBJECT([key, val[, key, val] …])
事例：

SELECT * from (
	SELECT *,json_value->'$.deptName' as deptName FROM dept
) t WHERE JSON_CONTAINS(deptName,JSON_OBJECT("depp","dd"));

3.5 JSON_ARRAY():创建JSON数组

比如我们添加这么一组数据到dept表中：

insert into dept VALUES(7,'部门9','{"deptName": ["1","2","3"], "deptId": "5", "deptLeaderId": "5"}');
insert into dept VALUES(7,'部门9','{"deptName": ["5","6","7"], "deptId": "5", "deptLeaderId": "5"}');

用法：JSON_ARRAY([val[, val] …])

事例：我们要查询deptName包含1的数据

SELECT * from dept WHERE JSON_CONTAINS(json_value->'$.deptName',JSON_ARRAY("1"))

3.6 JSON_TYPE():查询某个json字段属性类型

用法：JSON_TYPE(json_val)
事例：比如我们想查询deptName的字段属性是什么

SELECT json_value->'$.deptName' ,JSON_TYPE(json_value->'$.deptName') as type from dept

3.7 JSON_KEYS():JSON文档中的键数组

用法：JSON_KEYS(json_value)
事例：比如我们想查询json格式数据中的所有key

SELECT JSON_KEYS(json_value) FROM dept

接下来的3种函数都是新增数据类型的：

JSON_SET(json_doc, path, val[, path, val] …)
JSON_INSERT(json_doc, path, val[, path, val] …)
JSON_REPLACE(json_doc, path, val[, path, val] …)

3.8 JSON_SET():将数据插入JSON格式中，有key则替换，无key则新增

这也是我们开发过程中经常会用到的一个函数
用法：JSON_SET(json_doc, path, val[, path, val] …)
事例：比如我们想针对id=2的数据新增一组：newData:新增的数据,修改deptName为新增的部门1

update dept set json_value=JSON_SET('{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2;

注意：json_doc如果不带这个单元格之前的值，之前的值是会新值被覆盖的，比如我们如果更新的语句换成:

update dept set json_value=JSON_SET('{"a":"1","b":"2"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2

我们可以看到这里json_doc是{“a”:“1”,“b”:“2”}，这样的话会把之前的单元格值覆盖后再新增/覆盖这个单元格字段

3.9 JSON_INSERT():插入值（往json中插入新值，但不替换已经存在的旧值）

用法：JSON_INSERT(json_doc, path, val[, path, val] …)
事例：

UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', '新增的部门2','$.newData2','新增的数据2') 
WHERE id=2

我们可以看到由于json_doc变化将之前的值覆盖了，新增了deptName和newData2.
如果我们再执行以下刚才的那个sql，只是换了value，我们会看到里面的key值不会发生变化。
因为这个函数只负责往json中插入新值，但不替换已经存在的旧值。

3.10 JSON_REPLACE()

用法：JSON_REPLACE(json_doc, path, val[, path, val] …)
用例：
如果我们要更新id=2数据中newData2的值为：更新的数据2

UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}', '$.newData2', '更新的数据2') WHERE id =2;

3.11 JSON_REMOVE():从JSON文档中删除数据

用法：JSON_REMOVE(json_doc, path[, path] …)
举例：删除key为a的字段。

UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "更新的数据2"}','$.a') WHERE id =2;
多云转晴%
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
MySQL新类型JSON的用法讲解
qi_ming88的博客
01-26 976
本文主要讲解Mysql新数据类型json以及相关语法操作
MySQL中json字段的操作方法
01-19
MySQL5.7.8中引入了json字段，这种类型的字段使用的频率比较低，但是在实际操作中，有些业务仍然在用，我们以此为例，介绍下json字段的操作方法： 还是从例子看起： mysql> create table test1(id int,info json); Query OK, 0 rows affected (0.02 sec) mysql> insert into test1 values (1,'{name:yeyz,age:26}'),(2,'{name:zhangsan,age:30}'),(3,'{name:lisi,age:35}');
MySQL---JSON的用法讲解
最新发布
qq_62377885的博客
05-15 1074
JSON 数据类型是 MySQL 5.7.8 开始支持的。在此之前，只能通过字符类型（CHAR，VARCHAR 或 TEXT ）来保存 JSON 文档。MySQL 8.0版本中增加了对JSON类型的索引支持。可以使用CREATE INDEX语句创建JSON类型的索引，提高JSON类型数据的查询效率。存储JSON文档所需的空间与存储LONGBLOB或LONGTEXT所需的空间大致相同。在MySQL 8.0.13之前，JSON列不能有非空的默认值。
MySQL——JSON类型
吴声子夜歌的博客
04-08 2362
如果json_doc参数不是一个有效的JSON文档，任何路径参数不是一个有效的路径表达式，one_or_all不是’one’或’all’，或者escape_char不是一个常量表达式，就会发生错误。如果插入的值中存在重复 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原则，会保留第一个 KEY，后面的将被丢弃掉。之前，这类数据不是单独的数据类型，会被存储为字符串。从指定文档中给定的路径处的JSON文档中提取值，并返回提取的值，可以将其转换为所需的类型。
Mysql Json 数据类型&函数
王文萱的博客
06-28 7651
Mysql Json 数据类型&函数
MYSQL中JSON类型介绍 | 京东物流技术团队
京东科技开发者
07-31 439
在mysql未支持json数据类型时，我们通常使用varchar、blob或text的数据类型存储json字符串，对mysql来说，用户插入的数据只是序列化后的一个普通的字符串，不会对JSON文档本身的语法合法性做检查，文档的合法性需要用户自己保证。在使用时需要先将整个json对象从数据库读取出来，在内存中完成解析及相应的计算处理，这种方式增加了数据库的网络开销并降低处理效率。
Mysql当中Json相关的函数详解
怪咖@的博客
04-30 1万+
从5.7开始，MySQL开始支持json类型，用于存储JSON数据。提供数据类型的同时也提供了很多关于json的函数供我们使用，本篇文章基本上都来源于官网;
Mysql5.7中JSON操作函数使用说明
09-09
以下是一些关于 MySQL 5.7 中 JSON 操作函数的详细说明： 1. **JSON 数据类型** JSON 数据类型允许你在表中存储 JSON 文档。例如，在创建 `article` 表时，`tags` 字段被定义为 JSON 类型，可以存储如 `["Mysql", ...
详解Mysql中的JSON系列操作函数
09-10
在MySQL中，可以通过JSON字段类型来存储JSON对象，同时提供了一系列的JSON操作函数，以满足查询、修改JSON数据的需求。 首先，创建含有JSON字段的表。以`article`表为例，包含`id`、`title`和`tags`字段，其中`tags...
MySQL5.7中的JSON基本操作指南
12-16
MySQL从5.7版本开始就支持JSON格式的数据，操作用起来挺方便的。 建表 在新建表时字段类型可以直接设置为json类型，比如我们创建一张表： mysql> CREATE TABLE `test_user`(`id` INT PRIMARY KEY AUTO_INCREMENT, ...
MySQL数据库基础：JSON函数各类操作一文详解
Huangjiazhen711的博客
11-04 5988
很多日常业务场景都会用到json文件作为数据存储起来，而mysql5.7以上就提供了存储json的支撑。往常存储json一般都保留在pg库或者是hive库里面，现在mysql有了支持的话基本业务都可以用mysql来实现。现在mysql8.x版本对json字符出处理已经做的非常完善了。现在就让我们来详细了解一下关于json数据数据类型mysql都有哪些函数能够对其进行操作。
MySQL5.7高级函数：JSON_ARRAYAGG和JSON_OBJECT的使用
weixin_55076626的博客
09-28 1676
1、王强和雨婷、张伟和蕾蕾互为情侣。3、雨婷和蕾蕾都是李明的亲人。2、王强和雨婷都是张伟的朋友。
MySQL 中 JSON 字段增删改查相关函数及使用演示，简单易学，5分钟上手
m0_70325779的博客
04-01 1606
JSON数据是我们在开发过程中几位常用的一种数据存储方式，主要以KV键值对的形式存储数据，在 MySQL5.7 之后，提供了JSON字段，在 MySQL8之后得到了很好的优化，下面就是JSON格式数据与的例子，以KV键值对的形式存储数据，每个键值对之间使用 "，" 隔开即可。我们先准备好一张 product 商品表，字段类型如下图所示，方便一会操作展示然后在表中存放一些数据，如下图，数据均为编造，仅为演示建表SQL和数据SQL如下，想动手尝试小伙伴们可自行CV。
mysql中的json查询
热门推荐
qq_42468039的博客
01-12 2万+
参考文章 MySQL 5.7新增对JSON支持 1，json格式 处理方式： select * from log where data->'$.id' = 142; 或 select data->'$.id' id,data->'$.name' name from log where data->'$.id' = 142; 2，json数组格式 select name from country where JSON_EXTRACT(name, '$[1]') LIKE '
mysql：列类型之json
火焰
02-22 4269
环境： window10 vs2022 .net 6 mysql 8.0.25 DBeaver 1. 认识json格式 json全称是: JavaScript Object Notation。它的格式有以下几点： 两个对象类型（[] or {}）； 四种标量 （string、number、true/false、null）； 根节点是对象或者是数组； 标量：true/false和null均全为小写形式； 标量：string可以有转移字符（"、\、/、\b、\f、\n、\r、\t、\u0000）； js
MySQL JsonArray in 查询
donggedeCSDN的博客
05-05 1187
{"answerContent": "张三", "topicContentId": 501778, "topicId": 1502}, {"answerContent": "23", "topicContentId": 501779, "topicId": 1503}, {"answerContent": "王五", "topicContentId": 501780, "topicId": 1504}]MySQL存储JsonArray 字符串，使用in查询指定属性信息。
mysql json格式数据操作
qq_41482600的博客
05-05 2753
mysql5.7以上提供了一种新的字段格式-json，大概是mysql想把非关系型和关系型数据库一口通吃，所以推出了这种非常好用的格式，这样，我们的很多基于mongoDb或者clickHouse的业务都可以用mysql去实现了。5.7的版本只是最基础的版本，对于海量数据的效率是远远不够的，不过这些都在mysql8.0解决了。 1、创建json格式字段 CREATE TABLE `dept` ( `id` int(11) NOT NULL, `dept` varchar(255) DEFAULT .
Mysql存储json格式数据
qq_46416934的博客
07-28 5372
Mysql5.7版本以后新增的功能，Mysql提供了一个原生的Json类型，Json值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（documentelements）的内部二进制（internalbinary）格式。在Json列插入或者更新的时候将会自动验证Json文本，未通过验证的文本将产生一个错误信息创建表t_user登录方式字段使用json格式，分为phone,wechat,qq,email,zhifubao等等。...
mysql的json操作
07-27
MySQL提供了一些函数来对JSON数据进行操作。其中一些常用的函数包括： 1. JSON_EXTRACT(json_doc, path)：用于从JSON数据中提取指定路径的值。例如，SELECT JSON_EXTRACT(content, '$.name') FROM json_table; 可以提取出json_table表中content字段中的name值。 2. JSON_ARRAYAGG(expr)：用于将多个表达式的结果合并为一个JSON数组。例如，SELECT JSON_ARRAYAGG(name) FROM json_table; 可以将json_table表中的name字段的值合并为一个JSON数组。 3. JSON_OBJECT(key, value, ...)：用于创建一个JSON对象。例如，SELECT JSON_OBJECT('name', name, 'age', age) FROM json_table; 可以创建一个包含name和age字段的JSON对象。 4. JSON_ARRAY(value, ...)：用于创建一个JSON数组。例如，SELECT JSON_ARRAY(name, age) FROM json_table; 可以创建一个包含name和age字段值的JSON数组。 5. JSON_REMOVE(json_doc, path\[, path\] ...)：用于移除JSON数据中指定路径的数据。例如，SELECT JSON_REMOVE(content, '$.address.city') FROM json_table WHERE id = 2; 可以移除json_table表中id为2的记录中address字段中的city值。 6. JSON_REPLACE(json_doc, path, val\[, path, val\] ...)：用于替换JSON数据中指定路径的值。例如，SELECT JSON_REPLACE(content, '$.address.street', 'xxx街道') FROM json_table WHERE id = 1; 可以将json_table表中id为1的记录中address字段中的street值替换为'xxx街道'。 7. JSON_SET(json_doc, path, val\[, path, val\] ...)：用于向JSON数据中插入数据。例如，SELECT JSON_SET(content, '$.address.street', 'xxx街道') FROM json_table WHERE id = 1; 可以向json_table表中id为1的记录中address字段中插入一个street字段，并设置其值为'xxx街道'。 这些函数可以帮助我们在MySQL中对JSON数据进行灵活的操作和处理。 #### 引用[.reference_title] - *1* *2* *3* [MySQL数据库基础：JSON函数各类操作一文详解](https://blog.csdn.net/Huangjiazhen711/article/details/127684284)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值