Mysql 5.7 新特性之 json 类型的增删改查操作和用法 - 主机乐

你会发现，虽然最后一条记录的json内容和其他两条记录的内容完全不一样甚至结构都不一样，但是依然能插入成功，因为mysql在插入时只对格式进行校验，格式通过了，就当成字符串存储，并不会考虑与其他记录是否保持一致，事实上这么做没必要。但这同时引入了一个使用时需要注意的事项，就是在定义表的时候就必须尽可能的表以及字段的功能，数据结构明确清楚，并且避免一个字段多种数据混合存储。

更新记录：update 追加内容：

mysqljson数据跟新操作根据json数据的类型范围分为列表操作和对象操作两种类型，都可以直接说那个merge操作。

下面我们试着给第一条记录追加一个manager属性，很显然这是一个对象操作,执行语句如下：

再给第三条记录追加一个对象：

更新记录：update 修改json属性：

mysql提供一个非常灵活的本地方法：json_set(doc,path,value),其中doc为要修改的json列，path为要修改的属性的对象定位表达式，value为新值，例如：

需要注意的是，当使用json_set（）方法去修改一整个对象或者数组时，mysql或将value当成字符串来解析保存，当你的value中包含”时，或自动进行转义，这样存储起来很浪费空间，同时也会产生问题，如果需要修改整个对象，建议使用Json_object(key，value，key2,value2….)来格式化你的对象，例如：

删除记录：

删除记录使用JSON_REMOVE(doc,path1，path2,…)方法,例如：

查询记录：

查询所有的属性key,使用json_keys(doc)方法：

或者：

当然，你也可以使用通配符‘*’来实现多值查询，例如：

结果如下：

需要注意的是，查询返回的结果始终是一个包含查询结果的数组！

扩展：

创建一个表 t1，basic_info 字段为JSON类型:

CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`basic_info` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

一、插入一条数据：

1、查找 JSON 中的某个字段：

比如查询 id=1 的 basic_info 中的 name 字段，可以用以下两种方式：

select basic_info->’$.name’ from t1 where id =1;
//或者
select JSON_EXTRACT(basic_info, ‘$.name’) from t1 where id =1;

mysql> select basic_info->’$.name’ from t1 where id =1;
+———————————+
| basic_info->’$.name’ |
+———————————+
| “小明” |
+———————————+
或者：
mysql> select JSON_EXTRACT(basic_info, ‘$.name’) from t1 where id =1;
+——————————————————+
| JSON_EXTRACT(basic_info, ‘$.name’) |
+——————————————————+
| “小明” |
+——————————————————+
1 row in set (0.04 sec)

2、根据JSON中的某个字段查询表中记录：

比如查询name为小明的记录：

select * from t1 where basic_info->’$.name’ = ‘小明’;
//或者
select * from t1 where JSON_EXTRACT(basic_info,’$.name’) = ‘小明’;

检索JSON数据的方法，文档：https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

二、修改 JSON

修改JSON中字段的方法，文档：https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html

1、修改 JSON 中的某个字段：

比如我们修改 id =1 的 basic_info 中的age为 10 岁：

可以使用JSON_REPLACE() 或者 JSON_SET() 函数：

update t1 set basic_info = JSON_REPLACE(basic_info, ‘$.age’, 10) where id =1;
//或者
update t1 set basic_info = JSON_SET(basic_info, ‘$.age’, 10) where id =1;

2、往 JSON 中插入一个新的字段：

比如往 basic_info 中插入一个性别“gender”字段：

可以使用JSON_INSERT() 或者 JSON_SET() 函数：

update t1 set basic_info = JSON_INSERT(basic_info, ‘$.gender’, ‘男’) where id =1;
//或者
update t1 set basic_info = JSON_SET(basic_info, ‘$.gender’, ‘男’) where id =1;

JSON_SET()，JSON_INSERT() 和JSON_REPLACE() 函数的区别：