MySQL 5.7 JSON 数据类型使用总结

最新推荐文章于 2024-07-31 03:53:25 发布

置顶

KevanLiu

最新推荐文章于 2024-07-31 03:53:25 发布

阅读量1.9w

点赞数 8

分类专栏： MySQL 文章标签： MySQL JSON

本文链接：https://blog.csdn.net/u011207553/article/details/88912219

版权

本文总结了MySQL 5.7中JSON数据类型的使用，包括数据类型的简介、简单示例，如数据准备、查询和修改，以及一系列JSON函数的概览，如JSON_KEYS、JSON_CONTAINS等。

摘要由CSDN通过智能技术生成

文章目录

一、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 -> '$.sister') as sister_type
from json_tab;
# 查询姓名以及他们的年龄
select json_info -> '$.name' as name, json_info -> '$.age' as age
from json_tab;
select json_info -> '$**.name' as name, json_info -> '$**.age' as age
from json_tab;
# -> 等价于 JSON_EXTRACT(column, path)
select JSON_EXTRACT(json_info, '$.name') as name, JSON_EXTRACT(json_info, '$.age') as age
from json_tab;
# 去掉双引号
select json_info ->> '$.name' as name, json_info