MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

文章目录

概述
JSON
数据类型的意义
JSON相关函数
测试
优化JSON查询
总结

概述

MySQL从5.7后引入了json数据类型以及json函数，可以有效的访问json格式的数据。json数据类型相对于字符串，具有以下优点：

1）对于json列数据提供自动校验json格式，错误格式会提示错误；

2）优化存储类型。数据以二进制方式保存，读取效率快；

3）允许通过键值或者数据索引查找对象，无需把整个数据读取出来；

除此之外，json还有以下特点：

1）json存储空间大致于longblob或longtext差不多；

2）mysql8.0.13之后，json允许默认值为null；

3）json列不能设置索引，可通过json中的键值设置索引来提高查询效率；

4）json中null、true、false必须使用小写。

JSON 数据类型的意义

其实，没有JSON数据类型的支持，我们一样可以通过varchar类型或者text等类型来保存这一格式的数据，其中肯定有较varchar或者text来存储此类型更优越的地方。

保证了 JSON 数据类型的强校验，JSON 数据列会自动校验存入此列的内容是否符合 JSON 格式，非正常格式则报错，而 varchar 类型和 text 等类型本身是不存在这种机制的。
MySQL 同时提供了一组操作 JSON 类型数据的内置函数。
更优化的存储格式，存储在 JSON 列中的 JSON 数据会被转成内部特定的存储格式，允许快速读取。
基于 JSON 格式的特征，支持修改指定的字段值。

JSON相关函数

分类	函数	语法	描述
创建JSON	JSON_ARRAY	JSON_ARRAY(val1, val2…)	创建json数组
	JSON_OBJECT	JSON_OBJECT(key1, value1, key2, value2…)	创建ison对象
	JSON_QUOTE	JSON_QUOTE(string)	将参数用双引号括起来
	JSON_UNQUOTE	JSON_UNQUOTE(json_val)	去掉结果的双引号
查询JSON	JSON_CONTAINS	JSON_CONTAINS(json_doc, val[, path])	指定path是否包含指定数据，包含返回1，否则返回0.如果有参数为NULL或path不存在，则返回null
	JSON_CONTAINS_PATH	JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)	检查是否存在指定路径，是否满足一个或者所有，存在返回1，否则返回0.如果有参数为null,则返回null。one_or_all："one"表示查询到一个即返回；"all"表示查询所有。
	JSON_EXTRACT	JSON_EXTRACT(json_field, path[, path] …)	提取son字段某个路径的值
	COLUMN->PATH	json_model -> ‘ $.name’	json_extract的简洁写法，MySQL 5.7.9开始支持
	COLUMN->>PATH	json_model ->> ‘$.name'	json_unquote(column -> path)的简洁写法
	JSON_KEYS	JSON_KEYS(json_field)	提取json中的键值为json数组
	JSON_SEARCH	JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])	按给定字符串关键字搜索json，返回匹配的路径查询包含指定字符串的paths，并作为一个json array返回。如果有参数为NUL或path不存在，则返回NULL。one_or_all："one"表示查询到一个即返回；"all"表示查询所有。search_str：要查询的字符串。可以用LIKE里的'%'或‘_’匹配。path：在指定path下查。
	JSON_LENGTH	JSON_LENGTH(json_doc[, path])	返回数组的长度，如果是object则是属性个数，常量则为1，1. 标量的长度为1；2. json array的长度为元素的个数；3. json object的长度为key的个数。
	JSON_DEPTH	JSON_DEPTH(json_doc)	返回doc深度空的json array、json object或标量的深度为1
	JSON_PRETTY	JSON_PRETTY(json_field)	返回格式化json数据
修改JSON	JSON_SET	JSON_SET(json_doc, path, val[, path, val] ...)	修改json_field数据中的指定path的值，存在修改，不存在插入
	JSON_ARRAY_APPEND	JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)	给指定的节点，添加元素，如果节点不是数组，则先转换成[doc]

MySQL里的JSON分为json array和json object。

$表示整个json对象（数组或者对象）

数组使用$[i] ，从0开始。
对象使用$.key

测试

创建测试表

CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `json_model` json DEFAULT NULL COMMENT 'json字符串',
  `test_field` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

插入数据

普通json字符串插入

INSERT INTO `t_test` (`id`, `json_model`, `test_field`) VALUES 
(1, '{\"id\": 22, \"age\": 11, \"name\": \"cuizb\"}', '111111S'),
(2, '{\"id\": 1, \"age\": 11, \"name\": \"cuizb\"}', '111111S');

使用JSON函数构建插入

INSERT INTO `t_test` (`json_model`, `test_field`) VALUES (JSON_OBJECT("id", 11, "name", "Java", "age", "22"), '111111S');

或者

insert into t_test (`json_model`, `test_field`) values (JSON_ARRAY("1","2","3"), "11")

查询数据

JSON_CONTAINS

select json_model, JSON_CONTAINS(json_model, '{"id": 22}') test1, JSON_CONTAINS(json_model, '{"id": 33}') test2 from t_test

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

JSON_CONTAINS_PATH

select json_model, JSON_CONTAINS_PATH(json_model, 'one', '$.id', '$.name') test1, JSON_CONTAINS_PATH(json_model, 'all', '$.id', '$.test_field') test2 from t_test

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

JSON_EXTRACT

select JSON_EXTRACT(json_model, '$.name') test1, JSON_EXTRACT(json_model, '$.dept') test2 from t_test

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

JSON_UNQUOTE

select JSON_UNQUOTE(JSON_EXTRACT(json_model, '$.name')) name1, JSON_EXTRACT(json_model, '$.name') name2 from t_test

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

COLUMN->PATH

等同于JSON_EXTRACT
COLUMN->>PATH

等同于JSON_UNQUOTE

JSON_SET

select json_model from t_test where id = 1;
update t_test set json_model = JSON_SET(json_model,'$.name','我是你哥') where id = 1;
select json_model from t_test where id = 1;

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

条件查询

元数据

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

查询某个path的值是否匹配

select * from t_test where JSON_EXTRACT(json_model, '$.optimizeContents') = JSON_ARRAY('bid');

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

查询某个path是否包含值

select * from t_test where JSON_CONTAINS(json_model, JSON_ARRAY('budget'), '$.optimizeContents')

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

优化JSON查询

查询某个path的值是否匹配的执行计划

EXPLAIN select * from t_test where JSON_EXTRACT(json_model, '$.optimizeContents') = JSON_ARRAY('bid');

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

从执行计划可以看到，查询类型是全表扫描，这样的效率是很低的，那么如何优化呢？

按照过往的思路，我们只要设计合理的索引就能避免全表扫描，但是 JSON 列不能创建索引

解决方案

官方给出的方法是：基于JSON 创建一个生成列（Generated Column），然后基于生成列创建索引，从而达到对 JSON 类型列加索引的效果。

生成列的值在插入数据时不需要设置，MySQL 会根据生成列关联的表达式自动计算填充。

第一步：创建生成列

alter table t_test add COLUMN json_model_value VARCHAR(50) as (json_model -> '$.optimizeContents')

生成列 json_model_value 的值根据表达式 json_model->'$.optimizeContents' 自动计算填充。

第二步：为生成列创建索引

alter table t_test add index idx_json_model_value (json_model_value)

第三步：使用索引字段来查询

select * from t_test where json_model_value = '["bid"]';

执行计划

EXPLAIN select * from t_test where json_model_value = '["bid"]';

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

总结

JSON 类型是 MySQL 5.7 版本新增的数据类型，用好 JSON 数据类型可以有效解决很多业务中实际问题。最后，重点内容：使用 JSON 数据类型，推荐用 MySQL 8.0.17 以上的版本，性能更好，同时也支持 Multi-Valued Indexes。

JSON 数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；
不要将有明显关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；
JSON 数据类型推荐用于存储不经常更新的静态数据。

概述

MySQL从5.7后引入了json数据类型以及json函数，可以有效的访问json格式的数据。json数据类型相对于字符串，具有以下优点：

1）对于json列数据提供自动校验json格式，错误格式会提示错误；

2）优化存储类型。数据以二进制方式保存，读取效率快；

3）允许通过键值或者数据索引查找对象，无需把整个数据读取出来；

除此之外，json还有以下特点：

1）json存储空间大致于longblob或longtext差不多；

2）mysql8.0.13之后，json允许默认值为null；

3）json列不能设置索引，可通过json中的键值设置索引来提高查询效率；

4）json中null、true、false必须使用小写。

JSON 数据类型的意义

保证了 JSON 数据类型的强校验，JSON 数据列会自动校验存入此列的内容是否符合 JSON 格式，非正常格式则报错，而 varchar 类型和 text 等类型本身是不存在这种机制的。
MySQL 同时提供了一组操作 JSON 类型数据的内置函数。
更优化的存储格式，存储在 JSON 列中的 JSON 数据会被转成内部特定的存储格式，允许快速读取。
基于 JSON 格式的特征，支持修改指定的字段值。

JSON相关函数

分类	函数	语法	描述
创建JSON	JSON_ARRAY	JSON_ARRAY(val1, val2…)	创建json数组
	JSON_OBJECT	JSON_OBJECT(key1, value1, key2, value2…)	创建ison对象
	JSON_QUOTE	JSON_QUOTE(string)	将参数用双引号括起来
	JSON_UNQUOTE	JSON_UNQUOTE(json_val)	去掉结果的双引号
查询JSON	JSON_CONTAINS	JSON_CONTAINS(json_doc, val[, path])	指定path是否包含指定数据，包含返回1，否则返回0.如果有参数为NULL或path不存在，则返回null
	JSON_CONTAINS_PATH	JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)	检查是否存在指定路径，是否满足一个或者所有，存在返回1，否则返回0.如果有参数为null,则返回null。one_or_all："one"表示查询到一个即返回；"all"表示查询所有。
	JSON_EXTRACT	JSON_EXTRACT(json_field, path[, path] …)	提取son字段某个路径的值
	COLUMN->PATH	json_model -> ‘ $.name’	json_extract的简洁写法，MySQL 5.7.9开始支持
	COLUMN->>PATH	json_model ->> ‘$.name'	json_unquote(column -> path)的简洁写法
	JSON_KEYS	JSON_KEYS(json_field)	提取json中的键值为json数组
	JSON_SEARCH	JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])	按给定字符串关键字搜索json，返回匹配的路径查询包含指定字符串的paths，并作为一个json array返回。如果有参数为NUL或path不存在，则返回NULL。one_or_all："one"表示查询到一个即返回；"all"表示查询所有。search_str：要查询的字符串。可以用LIKE里的'%'或‘_’匹配。path：在指定path下查。
	JSON_LENGTH	JSON_LENGTH(json_doc[, path])	返回数组的长度，如果是object则是属性个数，常量则为1，1. 标量的长度为1；2. json array的长度为元素的个数；3. json object的长度为key的个数。
	JSON_DEPTH	JSON_DEPTH(json_doc)	返回doc深度空的json array、json object或标量的深度为1
	JSON_PRETTY	JSON_PRETTY(json_field)	返回格式化json数据
修改JSON	JSON_SET	JSON_SET(json_doc, path, val[, path, val] ...)	修改json_field数据中的指定path的值，存在修改，不存在插入
	JSON_ARRAY_APPEND	JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)	给指定的节点，添加元素，如果节点不是数组，则先转换成[doc]

MySQL里的JSON分为json array和json object。

$表示整个json对象（数组或者对象）

数组使用$[i] ，从0开始。
对象使用$.key

测试

创建测试表

CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `json_model` json DEFAULT NULL COMMENT 'json字符串',
  `test_field` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

插入数据

普通json字符串插入

INSERT INTO `t_test` (`id`, `json_model`, `test_field`) VALUES 
(1, '{"id": 22, "age": 11, "name": "cuizb"}', '111111S'),
(2, '{"id": 1, "age": 11, "name": "cuizb"}', '111111S');

使用JSON函数构建插入

INSERT INTO `t_test` (`json_model`, `test_field`) VALUES (JSON_OBJECT("id", 11, "name", "Java", "age", "22"), '111111S');

或者

insert into t_test (`json_model`, `test_field`) values (JSON_ARRAY("1","2","3"), "11")

查询数据

JSON_CONTAINS

select json_model, JSON_CONTAINS(json_model, '{"id": 22}') test1, JSON_CONTAINS(json_model, '{"id": 33}') test2 from t_test

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

JSON_CONTAINS_PATH

select json_model, JSON_CONTAINS_PATH(json_model, 'one', '$.id', '$.name') test1, JSON_CONTAINS_PATH(json_model, 'all', '$.id', '$.test_field') test2 from t_test

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

JSON_EXTRACT

select JSON_EXTRACT(json_model, '$.name') test1, JSON_EXTRACT(json_model, '$.dept') test2 from t_test

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

JSON_UNQUOTE

select JSON_UNQUOTE(JSON_EXTRACT(json_model, '$.name')) name1, JSON_EXTRACT(json_model, '$.name') name2 from t_test

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

COLUMN->PATH

等同于JSON_EXTRACT
COLUMN->>PATH

等同于JSON_UNQUOTE

JSON_SET

select json_model from t_test where id = 1;
update t_test set json_model = JSON_SET(json_model,'$.name','我是你哥') where id = 1;
select json_model from t_test where id = 1;

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

条件查询

元数据

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

查询某个path的值是否匹配

select * from t_test where JSON_EXTRACT(json_model, '$.optimizeContents') = JSON_ARRAY('bid');

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

查询某个path是否包含值

select * from t_test where JSON_CONTAINS(json_model, JSON_ARRAY('budget'), '$.optimizeContents')

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

优化JSON查询

查询某个path的值是否匹配的执行计划

EXPLAIN select * from t_test where JSON_EXTRACT(json_model, '$.optimizeContents') = JSON_ARRAY('bid');

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

从执行计划可以看到，查询类型是全表扫描，这样的效率是很低的，那么如何优化呢？

按照过往的思路，我们只要设计合理的索引就能避免全表扫描，但是 JSON 列不能创建索引

解决方案

官方给出的方法是：基于JSON 创建一个生成列（Generated Column），然后基于生成列创建索引，从而达到对 JSON 类型列加索引的效果。

生成列的值在插入数据时不需要设置，MySQL 会根据生成列关联的表达式自动计算填充。

第一步：创建生成列

alter table t_test add COLUMN json_model_value VARCHAR(50) as (json_model -> '$.optimizeContents')

生成列 json_model_value 的值根据表达式 json_model->'$.optimizeContents' 自动计算填充。

第二步：为生成列创建索引

alter table t_test add index idx_json_model_value (json_model_value)

第三步：使用索引字段来查询

select * from t_test where json_model_value = '["bid"]';

执行计划

EXPLAIN select * from t_test where json_model_value = '["bid"]';

MySQL的JSON数据类型介绍以及JSON的解析查询 - Java技术债务

总结

JSON 数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；
不要将有明显关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；
JSON 数据类型推荐用于存储不经常更新的静态数据。

本文作者：Java技术债务
原文链接： https://cuizb.top/myblog/article/1654955150
版权声明： 本博客所有文章除特别声明外，均采用 CC BY 3.0 CN协议进行许可。转载请署名作者且注明文章出处。

Java技术债务

MySQL的JSON数据类型介绍以及JSON的解析查询

文章目录

概述

JSON 数据类型的意义

JSON相关函数

测试

创建测试表

插入数据

查询数据

条件查询

优化JSON查询

解决方案

总结

概述

JSON 数据类型的意义

JSON相关函数

测试

创建测试表

插入数据

查询数据

条件查询

优化JSON查询

解决方案

总结

Java技术债务

最新评论