json数据的增删改查

创建一个表class

DROP TABLE IF EXISTS `class`;

CREATE TABLE `class` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL COMMENT '用户名',
`desc` json NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic COMMENT '班级表';

1插入：

1.1    insert into class(`id` ,`name`,`desc`) values (1 ,'jimao','{"sex":"1","high":"183","work":"programmer"}')

 1.2可以使用JSON_OBJECT()函数构造json对象

INSERT INTO class(`name`,`desc`) VALUES('liming', JSON_OBJECT("sex", 0, "age", 17));

 1.3使用JSON_ARRAY()函数构造json数组

 INSERT INTO class(`name`,`desc`) VALUES('guozihao', JSON_OBJECT("sex", 1, "age", 25, "tag", JSON_ARRAY(3,5,90)));

2查询

2.1查询所有数据

select * from class

2.2查询json中的单个值

这是发现desc是关键字，所以重新删掉建新表

DROP TABLE IF EXISTS `class`;

CREATE TABLE `class` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL COMMENT '用户名',
`detail` json NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic COMMENT '班级表';

insert into class(`id` ,`name`,`detail`) values (1 ,'jimao','{"sex":"1","high":"183","work":"programmer"}');

INSERT INTO class(`name`,`detail`) VALUES('liming', JSON_OBJECT("sex", 0, "age", 17));

INSERT INTO class(`name`,`detail`) VALUES('guozihao', JSON_OBJECT("sex", 1, "age", 25, "tag", JSON_ARRAY(3,5,90)));

SELECT *  FROM class

SELECT  JSON_EXTRACT(detail, '$.age'), JSON_EXTRACT(detail, '$.sex'), JSON_EXTRACT(detail, '$.tag[0]')from  class

 等同于select name, detail->'$.age', detail->'$.sex', detail->'$.tag[0]' from class;

 

2.3 查询结果去掉双引号

可以用JSON_UNQUOTE函数将双引号去掉

select name, JSON_UNQUOTE(detail->'$.sex') from class

 或者用->>来取引号中的值

select name, detail->>'$.sex' from class ;

 

按照json中字段的属性关联查询，注意sex的值带引号和不带引号是两种值

 select name, detail->>'$.age' from class where detail->'$.age'=28

 

可以对仪表新增虚拟列

ALTER TABLE `class` ADD `sex` VARCHAR(50) GENERATED ALWAYS AS (detail->>'$.sex') VIRTUAL

 然后用select name,sex FROM class where sex='1'; 查询出对应的结果

 3更新

3.1JSON_INSERT()插入新值，但是不会覆盖已经存在的值

更新前的页面

 更新后的页面

 更新语句

UPDATE class SET detail = JSON_INSERT(detail,'$.age',35, '$.sex','1','$.high', '179','$.money','1800') where id=1;

 3.2JSON_SET()插入新值，并覆盖已经存在的值

更新前的表

更新后的表

 更新语句

 UPDATE class SET detail = JSON_SET(detail,'$.sex','2','$.paymoney2', '900') where id=2;

3.3JSON_REPLACE：只替换存在的值

更改前

 更改后

sql:UPDATE class SET detail = JSON_REPLACE(detail, '$.sex', 9, '$.tag', '[1,2,3]') where id=3;

 4删除

移除json中的某个元素

删除前的表

 删除后的表

 sql:

UPDATE class SET detail  = JSON_REMOVE(detail, '$.paymoney', '$.paymoney2') where id=2;