MYSQL JSON数据类型详解

前言：

从MySQL 5.7.8开始支持原生JSON（RFC 7159规范定义）数据类型，可以更加高效的存储和管理JSON文档。

与在字符数据类型中存储json数据相比，原生JSON数据类型提供了自动的格式验证以及优化的存储格式，可以快速访问文档中的元素节点。

JSON数据最大长度不能超过max_allowed_packet的限制。

1、快速开始：

常见的json数据有以下格式：

JSON 数组包含一系列用逗号分隔并用[  ]字符括起来的值

["abc", 10, null, true, false]

JSON 对象包含一组键值对，用逗号分隔，并用 {  } 字符括起来

{"k1": "value", "k2": 10}

创建表:

create table study_json_user(    id        bigint not null auto_increment primary key,    user_info json   not null comment '用户信息',    hobby     json   null comment '爱好') comment '学习json的用户';

新增数据:

insert into study_json_user (user_info, hobby)values ('{  "name": "张三",  "gender": "男",  "age": 30,  "birthdate": "1992-01-01",  "nationality": "中国",  "address": {    "street": "南京路123号",    "city": "上海市",    "province": "上海市",    "postalCode": "123456"  },  "contact": {    "phone": "13800138000",    "email": "zhangsan@example.com"  },  "occupation": "软件工程师",  "education": "本科",  "height": 175}',        '[          "coding",          "reading"        ]');

查询数据:

select *from study_json_userwhere json_extract(user_info, '$.name') = '张三';select *from study_json_userwhere user_info -> '$.name' = '张三';

        json_extract 函数以JSON格式提取字段中的值，$.name表达式中$代表user_info，name表示user_info中的name属性。

        json_extract也可以用 -> 替换。

修改数据:

JSON_SET(), JSON_REPLACE(), or JSON_REMOVE()

update study_json_userset user_info = json_set(user_info, '$.name', '李四')where id = 1;

删除数据:

update study_json_userset hobby = null;

注:以上函数对于字符类型（VARCHAR，TEXT...）字段也同样生效。

2,进阶使用

user_info中有很多属性,当我们想对某个参数进行搜索时，如果使用下列函数查询是需要全表搜索的，因为我们没对被查询字段加索引。

json_extract(user_info, '$.name') = '张三'; 

我们可以加一个虚拟列

alter table study_json_user    add column name varchar(50) generated always as (user_info -> '$.name') virtual null comment '姓名';

该列由user_info中的name属性构成。virtual代表该列的数据不会存在数据库中，而是使用到该列式从我们定义的表达式（generated always as (user_info -> '$.name')）生成。与virtual对应的是stored，代表该列的数据会存在数据库中。

回到一开始的目的，我们要对name进行搜索

select *from study_json_userwhere name = '张三'

查询结果为空。查询数据库中的name字段数据，发现name字段数据外层有引号。

所以在这里我们要用到JSON_UNQUOTE()，等同于 ->>

用来取消 JSON 值的引号，并以 utf8mb4 字符串形式返回结果。

修改生成name列的sql语句为：

alter table study_json_user    add column name varchar(50) generated always as (json_unquote(user_info -> '$.name') ) stored null comment '姓名';

执行查询能正确返回结果，我们查询执行结果，发现是全表扫描：

explainselect *from study_json_userwhere name = '张三'

回到一开始的目的，我们要对name进行搜索，现在有了name列，我们还需要将name列设置为索引。

alter table study_json_user    add index idx_name (name);

再次查询，发现设置的索引生效了：

注：当只对json数据中的某些字段进行索引时，用字符类型（VARCHAR...）存储更节约空间,因为JSON格式除了存储元数据，还会花额外的空间存储JSON相关的结果，用于快速访问数据而不是实时解析JSON。

当我们更新user_info中的数据后,发现name数据随着更新了,我们只需要关注user_info中的数据即可。

update study_json_userset user_info ='{  "name": "钱七",  "gender": "男",  "age": 30,  "birthdate": "1992-01-01",  "nationality": "中国",  "address": {    "street": "南京路123号",    "city": "上海市",    "province": "上海市",    "postalCode": "123456"  },  "contact": {    "phone": "13800138000",    "email": "zhangsan@example.com"  },  "occupation": "软件工程师",  "education": "本科",  "height": 175}'where id = 1

当我们手动更新name字段时，发现报错了，所以在各类ORM框架中在insert，update时要排除掉该字段。

update study_json_userset name='赵六'where id = 1;

当user_info中不存在name属性时，user_info -> '$.name'得到的值是mysql中的null值。

当user_info中的name属性为null时，user_info -> '$.name'得到的值是JSON中的null值。

当user_info中不存在name属性时，json_unquote(user_info -> '$.name')的值为mysql中的null值。

当user_info中的name属性为null时，json_unquote(user_info -> '$.name')的值为‘null’，一个字符串的null。

所以我们要对这两种情况做额外判断，统一设置为mysql中的null类型：

（json_type返回值为JSON数据内字段类型）

case     when user_info -> '$.name' is null or json_type(user_info -> '$.name') = 'NULL'          then null     else user_info ->> '$.name' end

3,总结

将上述的表结构整合：

create table study_json_user(    id        bigint                        not null auto_increment primary key,    user_info text                          not null comment '用户信息',    name      varchar(50) generated always as (case    when user_info -> '$.name' is null or json_type(user_info -> '$.name') = 'NULL'        then null    else user_info ->> '$.name' end) stored null comment '姓名',    hobby     text                          null comment '爱好',    index idx_name (`name`)) comment '学习json的用户';

（将user_info的JSON格式替换成了text，我们用不到JSON格式的特性，所以用text节约少许存储空间） 

查询语句：

select *from study_json_userwhere name = '张三'

extra:

​​​​​​​mysql的JSON格式各版本差异特性-CSDN博客

mysql-json学习记录-CSDN博客