mysql json增删改查_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;