MySQL对JSON数据的增删改查

1.建表

在新建表时字段类型可以直接设置为json类型，比如我们创建一张表：

CREATE TABLE `test_json` (
`id` INT PRIMARY KEY AUTO_INCREMENT, 
`name` VARCHAR ( 50 ) NOT NULL, 
`info` JSON,
`info_char` VARCHAR ( 1024 ) NULL
);

json类型字段可以为NULL

2.插入数据

INSERT INTO test_json(`name`, `info`, `info_char`) VALUES('xiaoming','{"sex": 1, "age": 18, "nick_name": "小萌"}', '{"sex": 1, "age": 18, "nick_name": "小萌"}');

json类型的字段必须时一个有效的json字符串, 可以使用JSON_OBJECT()函数构造json对象：

INSERT INTO test_json(`name`, `info`, `info_char`) VALUES('xiaohua', JSON_OBJECT("sex", 0, "age", 17), JSON_OBJECT("sex", 0, "age", 17));

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

INSERT INTO test_json(`name`, `info`, `info_char`) VALUES('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)), JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)));

3.查询

表达式： 对象为json列->'$.键', 数组为json列->'$.键[index]'
等价于：对象为JSON_EXTRACT(json列 , '$.键') ，数组为JSON_EXTRACT(json列 , '$.键[index]')

select name, JSON_EXTRACT(info_char, '$.nick_name'), JSON_EXTRACT(info_char, '$.sex'), JSON_EXTRACT(info_char, '$.tag[0]') from test_json;

不过看到上面"小萌"是带双引号的，这不是我们想要的，可以用JSON_UNQUOTE函数将双引号去掉

select name, JSON_UNQUOTE(JSON_EXTRACT(info_char, '$.nick_name')) from test_json where name='xiaoming'; 

当然属性也可以作为查询条件:

select name, JSON_EXTRACT(info_char, '$.nick_name') from test_json where JSON_UNQUOTE(JSON_EXTRACT(info_char, '$.nick_name'))='小萌'; 

4.可以通过虚拟列对JSON类型的指定属性进行快速查询。

创建虚拟列:(可以方便查询，但是不能修改,只能修改原始数据)

ALTER TABLE `test_json` ADD `nick_name` VARCHAR(50) GENERATED ALWAYS AS (JSON_EXTRACT(info_char, '$.nick_name')) VIRTUAL;

使用时和普通类型的列查询是一样:

select name,nick_name from test_json where nick_name='小萌'; 

5.更新, 使用JSON_INSERT()插入新值，但不会覆盖已经存在的值

UPDATE test_json SET info_char = JSON_INSERT(info_char, '$.sex', 1, '$.nick_name', '小花') where id=2;

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

UPDATE test_json SET info_char = JSON_SET(info_char, '$.sex', 0, '$.nick_name', '小张') where id=3;

使用JSON_REPLACE()只替换存在的值

UPDATE test_json SET info_char = JSON_REPLACE(info_char, '$.sex', 1, '$.tag', '[1,2,3]') where id=2;

可以看到tag没有更新进去

6. 删除, 使用JSON_REMOVE()删除JSON元素

UPDATE test_json SET info_char = JSON_REMOVE(info_char, '$.sex', '$.tag') where id=1;