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

Java技术债务

发布于 2022-08-09 14:19:01

10.2K0

发布于 2022-08-09 14:19:01

文章被收录于专栏：Java技术债务

概述

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。oneor_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

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

JSON_EXTRACT

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

JSON_UNQUOTE

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

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;

条件查询

元数据

查询某个path的值是否匹配

查询某个path是否包含值

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

优化JSON查询

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

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

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

按照过往的思路，我们只要设计合理的索引就能避免全表扫描，但是 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"]';

总结

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

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

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-06-11，如有侵权请联系 cloudcommunity@tencent.com 删除

json

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度