侧边栏壁纸
博主头像
孔子说JAVA博主等级

成功只是一只沦落在鸡窝里的鹰,成功永远属于自信且有毅力的人!

  • 累计撰写 292 篇文章
  • 累计创建 132 个标签
  • 累计收到 4 条评论

目 录CONTENT

文章目录

MySql5.7开始引入的json类型基础操作实战教程

孔子说JAVA
2022-07-10 / 0 评论 / 0 点赞 / 87 阅读 / 10,027 字 / 正在检测是否收录...

MySQL从5.7.8后引入了一种新的数据类型-json,并提供很多json相关的函数,可以有效的访问json格式的数据,至此MySql有了非关系型数据的访问能力,之前很多基于mongoDb或者clickHouse的业务都可以用mysql去实现了。5.7.8的版本只是基础版本,对于海量数据的效率比较低,所以Mysql8.0对其做了很好的优化,本文所有示例以MySql8.0为基础。

  • json作为一种自带结构的文本使得结构信息可以与数据库解耦。
  • 注意:5.7.8之前的版本是没有json类型的。

image-1657425459157

1、JSON类型的优势

之前在设计数据表的时候,为了应对表的扩展,我们经常性的做法是设置一个varchar类型的字段,存放一个json字符串,这样如果有新增的属性就放入这个字段中,有效的解决了数据表的扩展问题,但是该方法的缺点是对放入该字段的属性无法检索及索引。

1.1 json类型的特性

Mysql5.7开始加入了json数据类型,相对于字符串具有以下特点:

  • 1)对于json列数据提供自动校验json格式,错误格式会提示错误,而varchar类型和text等类型本身是不存在这种机制的。
  • 2)MySQL同时提供了一组操作JSON类型数据的内置函数。
  • 3)更优化的存储格式,数据以二进制方式保存,读取效率快。(存储在JSON列中的JSON数据会被转成内部特定的存储格式,允许快速读取)
  • 4)允许通过键值或者数据索引查找对象,无需把整个数据读取出来。
  • 5)可以基于JSON格式的特征支持修改特定的键值。(即不需要把个数据拿出来放到程序中遍历替换再塞回去,MySQL内置的函数允许你通过一条SQL语句就能搞定)

除此之外,json类型还有以下特点:

  • 1)json存储空间大致和longblob或longtext差不多;
  • 2)mysql8.0.13之后,json允许默认值为null;
  • 3)json列不能设置索引,可通过json中的键值设置索引来提高查询效率;
  • 4)json中null、true、false必须使用小写。

1.2 json类型的优缺点

1)优点

  • 不需要设计复杂的关系表。
  • 提高开发的效率。
  • 可以任意的增减字段而无需修改数据表结构,甚至可以减少数据表的设计。
  • 保存数据方便,只需要组装成数组。
  • 可以减少数据表的查询,减少关联查询,一个查询的结果就可以代替几个查询的结果,降低数据库服务器的压力。

2)缺点

  • 从json中去统计某个字段数据之类的很麻烦,而且效率低。
  • 查询相对效率较低,操作复杂。
  • 更新其中某个字段效率较低,不适合存储业务逻辑复杂的数据。
  • 统计数据复杂,建议需要做报表的数据不要存json。

2、JSON类型分类

MySQL里的JSON分为 json arrayjson object$ 表示整个json对象(数组或者对象)。

  • 对于数组json array,在索引数据时用下标(从0开始)访问,数组使用 $[i]
  • 对于对象json object,在索引数据时用键值,对象使用 $.key,对于含有特殊字符的key要用 "" 括起来,比如 $."my name"

如以下的json是一个数组类型:

[3, {"a": [5, 6], "b": 10}, [99, 100]]

我们在操作该json时,获取相关值的方式如下:

-- 数组中的第一个值
$[0]:3

-- 数组中的第二个值
$[1]: {"a": [5, 6], "b": 10}

-- 数组中的第三个值
$[2] :[99, 100]

-- 数组中的第四个值,不存在所以为null
$[3] : NULL

-- 数组中的第二个值为对象,取该对象a属性的值
$[1].a:[5, 6]

-- 数组中的第二个值为对象,该对象a属性的值为数组,取该a属性的第二个值
$[1].a[1]:6

-- 数组中的第二个值为对象,该对象b属性的值为一个数值
$[1].b:10

-- 数组中的第三个值为数组,取该数组的第一个值
$[2][0]:99

3、JSON类型使用实战

3.1 数据库版本判断

在操作之前先判断自己的数据库是否为5.7.8及以上版本,如果不是,需要升级版本。通过 select VERSION(); 可以查看自己数据库的版本。

如下查询的版本为 5.7.31,不符合版本最低为 5.7.8的要求,所以无法创建json类型的字段。

image-1657423515042

下面查询的数据库版本为8.0.29,符合要求,可以创建json类型的字段。

image-1657423629514

3.2 创建含有json类型字段的表

image-1657422973636

建表语句,其中 info 字段为json类型,

DROP TABLE IF EXISTS `tb_emp`;
CREATE TABLE `tb_emp`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `ename` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `info` json NULL COMMENT '员工信息',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_emp
-- ----------------------------
INSERT INTO `tb_emp` VALUES (1, 'SMITH', '{\"job\": \"CLERK\", \"mgr\": 7902, \"sal\": 800.0, \"comm\": null, \"empno\": 7369, \"ename\": \"SMITH\", \"deptno\": 20, \"hiredate\": 345830400000}');
INSERT INTO `tb_emp` VALUES (2, 'ALLEN', '{\"job\": \"SALESMAN\", \"mgr\": 7698, \"sal\": 1600.0, \"comm\": 300.0, \"empno\": 7499, \"ename\": \"ALLEN\", \"deptno\": 30, \"hiredate\": 351446400000}');
INSERT INTO `tb_emp` VALUES (3, 'WARD', '{\"job\": \"SALESMAN\", \"mgr\": 7698, \"sal\": 1250.0, \"comm\": 500.0, \"empno\": 7521, \"ename\": \"WARD\", \"deptno\": 30, \"hiredate\": 351619200000}');
INSERT INTO `tb_emp` VALUES (4, 'JONES', '{\"job\": \"MANAGER\", \"mgr\": 7839, \"sal\": 2975.0, \"comm\": null, \"empno\": 7566, \"ename\": \"JONES\", \"deptno\": 20, \"hiredate\": 354988800000}');
INSERT INTO `tb_emp` VALUES (5, 'MARTIN', '{\"job\": \"SALESMAN\", \"mgr\": 7698, \"sal\": 1250.0, \"comm\": 1400.0, \"empno\": 7654, \"ename\": \"MARTIN\", \"deptno\": 30, \"hiredate\": 370454400000}');
INSERT INTO `tb_emp` VALUES (6, 'BLAKE', '{\"job\": \"MANAGER\", \"mgr\": 7839, \"sal\": 2850.0, \"comm\": null, \"empno\": 7698, \"ename\": \"BLAKE\", \"deptno\": 30, \"hiredate\": 357494400000}');
INSERT INTO `tb_emp` VALUES (7, 'CLARK', '{\"job\": \"MANAGER\", \"mgr\": 7839, \"sal\": 2450.0, \"comm\": null, \"empno\": 7782, \"ename\": \"CLARK\", \"deptno\": 10, \"hiredate\": 360864000000}');
INSERT INTO `tb_emp` VALUES (8, 'SCOTT', '{\"job\": \"ANALYST\", \"mgr\": 7566, \"sal\": 3000.0, \"comm\": null, \"empno\": 7788, \"ename\": \"SCOTT\", \"deptno\": 20, \"hiredate\": 545756400000}');
INSERT INTO `tb_emp` VALUES (9, 'KING', '{\"job\": \"PRESIDENT\", \"mgr\": null, \"sal\": 5000.0, \"comm\": null, \"empno\": 7839, \"ename\": \"KING\", \"deptno\": 10, \"hiredate\": 374774400000}');
INSERT INTO `tb_emp` VALUES (10, 'TURNER', '{\"job\": \"SALESMAN\", \"mgr\": 7698, \"sal\": 1500.0, \"comm\": 0.0, \"empno\": 7844, \"ename\": \"TURNER\", \"deptno\": 30, \"hiredate\": 368726400000}');
INSERT INTO `tb_emp` VALUES (11, 'ADAMS', '{\"job\": \"CLERK\", \"mgr\": 7788, \"sal\": 1100.0, \"comm\": null, \"empno\": 7876, \"ename\": \"ADAMS\", \"deptno\": 20, \"hiredate\": 548694000000}');
INSERT INTO `tb_emp` VALUES (12, 'JAMES', '{\"job\": \"CLERK\", \"mgr\": 7698, \"sal\": 950.0, \"comm\": null, \"empno\": 7900, \"ename\": \"JAMES\", \"deptno\": 30, \"hiredate\": 376156800000}');
INSERT INTO `tb_emp` VALUES (13, 'FORD', '{\"job\": \"ANALYST\", \"mgr\": 7566, \"sal\": 3000.0, \"comm\": null, \"empno\": 7902, \"ename\": \"FORD\", \"deptno\": 20, \"hiredate\": 376156800000}');
INSERT INTO `tb_emp` VALUES (14, 'MILLER', '{\"job\": \"CLERK\", \"mgr\": 7782, \"sal\": 1300.0, \"comm\": null, \"empno\": 7934, \"ename\": \"MILLER\", \"deptno\": 10, \"hiredate\": 380563200000}');

SET FOREIGN_KEY_CHECKS = 1;

3.3 基础查询示例

示例1:查询JSON中的某几个字段

  • 查看所有员工的编号、名称、工作、薪水
SELECT id,ename,info->'$.job',info->'$.sal' from tb_emp;

SELECT id,ename,JSON_EXTRACT(info,'$.job'),JSON_EXTRACT(info,'$.sal') from tb_emp;

SELECT id,ename,JSON_EXTRACT(info,'$.job','$.sal') from tb_emp; 
  • 查询id值为7788的员工的名称是否为SCOTT
SELECT JSON_CONTAINS(info,'{"ename":"SCOTT"}') 
from tb_emp 
WHERE id= 8
  • 查询指定字段中是否包含ename和dno中的任何一个
SELECT JSON_CONTAINS_PATH(info, 'one','$.ename','$.dno')
from tb_emp
WHERE id= 8
  • 查询指定字段中是否同时包含ename和dno
SELECT JSON_CONTAINS_PATH(info, 'all','$.ename','$.dno')
from tb_emp
WHERE id= 8

示例2:作为查询条件

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

  • 查询部门编号为10的员工的详细信息
select * from tb_emp where info->'$.deptno'=10;
  • 查询部门编号为10且mgr为null的员工的详细信息(涉及多个条件的查询)
select * from tb_emp where info->'$.deptno'=10 and info->'$.ename'='CLARK';
  • 查询部门编号为10且id为7的员工的详细信息
select * from tb_emp where info->'$.deptno'=10 and id =7;
  • 查询薪水大于3000的员工的信息
select * from tb_emp where JSON_EXTRACT(info, '$.sal')>3000;
  • 查询薪水等于5000的员工的信息
select * from tb_emp where JSON_CONTAINS(info, JSON_OBJECT('sal',5000))
  • 查询JSON字段中所有的键
select JSON_KEYS(info) FROM tb_emp;
  • 模糊查询,JSON 字段的模糊搜索仅支持 %str% 格式,也因此其模糊搜索时索引是失效的
select * from tb_emp where info->'$.ename' like '%to%';

3.4 多表关联查询

如果涉及到关联表查询也是支持的,这里我们创建2张包含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;


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 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"}');

insert into dept_leader VALUES(10,'leader1','{"name": "王一", "id": "1", "leaderId": "1"}');
insert into dept_leader VALUES(20,'leader2','{"name": "王二", "id": "2", "leaderId": "3"}');
insert into dept_leader VALUES(30,'leader3','{"name": "王三", "id": "3", "leaderId": "4"}');
insert into dept_leader VALUES(40,'leader4','{"name": "王四", "id": "4", "leaderId": "5"}');
insert into dept_leader VALUES(50,'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' ;
0

评论区