mysql 5.7 新特性之 json 类型 基本操作

近几年来，nosql大行其道，json更是火的一塌糊涂，作为数据库的元老，mysql在5.7版本中添加了对json数据的支持。这片博客主要记录对mysql json类型的学习笔记和总结心得。

首先创建一个数据库learnjson，并创建一张表json_start：

create table json_start ( id int auto_increment PRIMARY KEY not null, person json not NULL );

插入数据 ：insert操作

在json_start表中我们添加了一个json类型的字段：person;下面我们就来插入一些数据，

json类型的数据插入方式和普通数据插入方式一样，不同的是插入的是json字符串:

INSERT INTO json_start VALUES(null,'{"coder":[{"name":"张三","age":"18","gender":"男"},{"name":"李四","age":"14","gender":"男"},{"name":"王五","age":"15","gender":"女"}], "teacher":[{"name":"刘二","age":"22","gender":"女"},{"name":"赵三","age":"23","gender":"男"},{"name":"王五","age":"15","gender":"女"}], "student":[{"name":"张三","age":"18","gender":"男"},{"name":"李四","age":"14","gender":"男"},{"name":"王五","age":"15","gender":"女"}] }');

需要注意的是，在插入json类型的字符串时，mysql会对json字符串进行格式校验，如果格式有误，就无法插入，并且默认情况下，json数据的大小也是有限制的，默认大小和LONGTEXT ,LONGBLOB 一样，同时他还受限于mysql配置项：max_allowed_packet. 的限制（默认为4M）.

我们观察数据库的结果：

数据已经添加到数据库中了，我们试着在插入几条数据：

INSERT INTO json_start VALUES(null,'{"handsome":[{"name":"山山","age":"18","gender":"男"},{"name":"张一山","age":"26","gender":"男"},{"name":"bug4j","age":"18","gender":"男"}], "beauty":[{"name":"刘亦菲","age":"18","gender":"女"},{"name":"xixi","age":"23","gender":"女"},{"name":"周冬雨","age":"15","gender":"女"}], "fatty":[{"name":"zhangqi","age":"38","gender":"男"},{"name":"pangci","age":"30","gender":"男"},{"name":"王五","age":"15","gender":"女"}] }'); INSERT INTO json_start VALUES(null, '[{"name":"奔驰","model":"C260","price":"200RMB"}, {"name":"劳斯莱斯","model":"定制版","price":"166RMB"}, {"name":"奥迪","model":"A6","price":"200000000RMB"}]' );

结果：

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

更新记录：update 追加内容：

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

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

UPDATE json_start set person = json_MERGE(person,'{"manager":[{"name":"laoda","age":"38","gender":"男"}]}') where id=1;

原理就是将原来的json对象和要添加的对象合并，稍后将会描述这其中的合并规则。执行结果如下：

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

UPDATE json_start set person = json_merge(person,'[{"name":"大众","model":"k1","price":"600000000RMB"}]') where id=3;

更新记录：追加内容到数组：

追加到数组可以使用JSON_APPEND()(mysql 5.7.9或更早的版本) 或者JSON_ARRAY_APPEND()方法，该操作将指定的对象添加到数组末尾，例如： update json_start set person = json_ARRAY_APPEND(person,"$",JSON_OBJECT("name","玛莎拉蒂","model","ah","price","168RMB")) where id=3; 执行结果：

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

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

UPDATE json_start set person = json_set(person,'$.coder[0].name','张三三三三三') where id = 1结果为：

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

UPDATE json_start set person = json_set(person,'$.coder[0]',JSON_OBJECT("age",18,"name","张sisisisi","gender","男")) where id = 1; UPDATE json_start set person = json_set(person,"$[0]",JSON_OBJECT("age",18,"name","张wuwuwuuwu","gender","男")) where id = 3;

执行结果为：

删除记录：

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

update json_start set person = json_REMOVE(person,"$[0]") where id = 3; update json_start set person = json_REMOVE(person,"$.coder[0]") where id = 1;执行结果为：

查询记录：

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

select id,json_keys(person) from json_start结果如下：

查询指定的记录中的某个属性或者值，使用Json_extract(doc,path)方法，例如：

select id,json_extract(person,"$.coder[0].name") from json_start where id = 1结果如下：

或者

select id,json_extract(person,"$.coder[0]") from json_start where id = 1

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

select id,json_extract(person,"$.coder[*].name") from json_start where id = 1 UNION All select id,json_extract(person,"$.*[*].name") from json_start where id = 1

结果如下：

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