MySQL 5.7 JSON 数据类型使用总结

一、JSON数据类型简介

从版本5.7.8开始，mysql开始支持json数据类型，json数据类型存储时会做格式检验，不满足json格式会报错，json数据类型默认值不允许为空。

二、简单使用示例

数据准备

create table json_tab
(
  id        int unsigned primary key auto_increment comment '主键',
  json_info json comment 'json数据',
  json_id   int generated always as (json_info -> '$.id') comment 'json数据的虚拟字段',
  index json_info_id_idx (json_id)
)
  comment 'json示例表';

insert into json_tab(json_info)
values ('{"id": 1, "name": "张三", "age": 18, "sister": [{"name": "张大姐", "age": 30}, {"name": "张二姐", "age": 20}]}');
insert into json_tab(json_info)
values (JSON_OBJECT('id', 2, 'name', '李四', 'age', 18, 'sister', JSON_ARRAY(JSON_OBJECT('name', '李大姐', 'age', 28), JSON_OBJECT('name', '李二姐', 'age', 25))));
insert into json_tab(json_info)
values ('{"id": 3, "name": "小明", "age": 18, "sister": [{"name": "小明大姐", "age": 25, "friend": [{"name": "大姐朋友一", "age": 25}, {"name": "大姐朋友二", "age": 25}]}, {"name": "小明二姐", "age": 20, "friend": [{"name": "二姐朋友一", "age": 22}, {"name": "二姐朋友二", "age": 21}]}]}');

json_id 是虚拟列，插入数据时不需要往该字段插入值，json数据类型不能直接建立索引，需要通过建立虚拟列再将索引建在虚拟列上这样的方式来建立索引；

json字段插入数据时有两种方式，一种是直接插入满足json格式的字符串，不符合json格式的字符串插入时会报错；另一种是通过JSON_OBJECT、JSON_ARRAY这两个json函数先构建好json数据再插入。

数据查询

# 先看看数据，注意虚拟列json_id，未插入值确显示有值
select * from json_tab;
select * from json_tab order by json_id desc;
select * from json_tab where json_info -> '$.name' = '李四';
# JSON_TYPE 函数判断JSON数据类型
select JSON_TYPE(json_info)               as info_type,
       JSON_TYPE(json_info -> '$.age')    as age_type,
       JSON_TYPE(json_info -> '$.name')   as name_type,
       JSON_TYPE(json_info -> '$