概述
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
-
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的值是否匹配
select * from t_test where JSON_EXTRACT(json_model, '$.optimizeContents') = JSON_ARRAY('bid');
-
查询某个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 数据类型推荐用于存储不经常更新的静态数据。
本文作者：Java技术债务
原文链接：https://www.cuizb.top/myblog/article/1654955150
版权声明： 本博客所有文章除特别声明外，均采用 CC BY 3.0 CN协议进行许可。转载请署名作者且注明文章出处。
JVM内存泄漏和内存溢出的原因
JVM常用监控工具解释以及使用
Redis 常见面试题（一）
ClickHouse之MaterializeMySQL引擎（十）
三种实现分布式锁的实现与区别
线程池的理解以及使用
号外！号外！
最近面试BAT，整理一份面试资料，覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。想获取吗？如果你想提升自己，并且想和优秀的人一起进步，感兴趣的朋友，可以在扫码关注下方公众号。资料在公众号里静静的躺着呢。。。
- 喜欢就收藏
- 认同就点赞
- 支持就关注
- 疑问就评论
一键四连，你的offer也四连
————————————————————————————————————————————————————————————————