今天一个兄弟谈说mongoDb,作为一个架构师,应该有对比思维,mysql5.7以上提供了一种新的字段格式-json,我们的很多基于mongoDb或者clickHouse的业务都可以用mysql去实现了。当然了,5.7的版本只是最基础的版本,对于海量数据的效率是远远不够的,不过这些都在mysql8.0解决了。今天我们就针对mysql的json数据格式操作做一个简单的介绍。

如何创建json格式字段
这里我们先创建一个简单的含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"}');



一般基础查询操作
1、使用 json字段名->’$.json属性’ 进行查询条件
举个例子:如果我想查询deptLeader=张五的数据,那么sql语句如下:

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';
1
查询出来的结果如下:

spring输出mysql字符串乱码 mysql输出json_json

 

2、如果涉及多个条件也是支持的
比如我想查dept为“部门3”和deptLeaderId=5的数据,sql如下:

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and dept='部门3';



查询和关系型数据库查询一致。

3、如果涉及json中多个字段关系查询
比如我想查询json格式中deptLeader=张五和deptId=5的数据

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';


 

spring输出mysql字符串乱码 mysql输出json_java_02


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' ;

一般函数查询操作
写到这里大家都发现了,我们查询的json都是整条json数据,这样看起来不是很方便,那么如果我们只想看json中的某个字段怎么办?

这样就引入了我们的第一个函数:json_extract(字段名,json字段名)

在详细介绍用法之前我们可以看看官网的函数介绍:

咱们可以看到官网介绍json_extract()这个函数很详细:Return data from JSON document

从json中返回字段

1、函数 json_extract():从json中返回想要的字段
用法:json_extract(字段名,$.json字段名)
事例:

select id,json_extract(json_value,'$.deptName') as deptName from dept;



结果:

spring输出mysql字符串乱码 mysql输出json_mongodb_03

 

 

2、函数JSON_CONTAINS():JSON格式数据是否在字段中包含特定对象
用法: JSON_CONTAINS(target, candidate[, path])
事例:如果我们想查询包含deptName=部门5的对象

select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))


结果:

spring输出mysql字符串乱码 mysql输出json_json_04

 

3、函数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"));



结果:

spring输出mysql字符串乱码 mysql输出json_json_05

 

4、函数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"))

结果:

spring输出mysql字符串乱码 mysql输出json_mongodb_06

 

5、函数JSON_TYPE():查询某个json字段属性类型
用法:JSON_TYPE(json_val)
事例:比如我们想查询deptName的字段属性是什么

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

结果:

spring输出mysql字符串乱码 mysql输出json_spring输出mysql字符串乱码_07

 

我们可以看到deptName对应的字段属性分别是什么

6、函数JSON_EXTRACT() :从JSON文档返回数据
这也是我们开发中会经常用到的一个函数
用法:

事例一:比如我们要查询deptName like ‘部门‘ 的数据
SELECT * FROM dept WHERE JSON_EXTRACT(json_value,'$.deptName') like '%部门%';

结果:

spring输出mysql字符串乱码 mysql输出json_json_08

 

7、函数JSON_KEYS() :JSON文档中的键数组
用法:JSON_KEYS(json_value)

事例:比如我们想查询json格式数据中的所有key

SELECT JSON_KEYS(json_value) FROM dept 

结果:

spring输出mysql字符串乱码 mysql输出json_数据库_09

 

接下来的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] …)

8、函数JSON_SET() :将数据插入JSON格式中,有key则替换,无key则新增
这也是我们开发过程中经常会用到的一个函数

用法:JSON_SET(json_doc, path, val[, path, val] …)

事例:比如我们想针对id=2的数据新增一组:newData:新增的数据,修改deptName为新增的部门1
sql语句如下:

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



结果:

spring输出mysql字符串乱码 mysql输出json_mongodb_10

 

注意: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”},这样的话会把之前的单元格值覆盖后再新增/覆盖这个单元格字段

结果:

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



结果:

spring输出mysql字符串乱码 mysql输出json_mongodb_11

 

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

10、函数JSON_REPLACE():
用法:JSON_REPLACE(json_doc, path, val[, path, val] …)

用例:
如果我们要更新id=2数据中newData2的值为:更新的数据2

sql语句如下:

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



结果:

spring输出mysql字符串乱码 mysql输出json_数据库_12

 

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;



结果:

spring输出mysql字符串乱码 mysql输出json_java_13


12、函数JSON_SEARCH() :用于在json格式中查询并返回符合条件的节点

这是一个非常强大的函数