前言：

从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_user where json_extract(user_info, '$.name') = '张三';  select * from study_json_user where 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_user set user_info = json_set(user_info, '$.name', '李四') where id = 1;

删除数据:

update study_json_user set 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_user where 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 '姓名';

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

explain select * from study_json_user where 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_user set 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_user set 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_user where name = '张三'

extra:

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

mysql-json学习记录-CSDN博客