概述

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来存储此类型更优越的地方。

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

JSON相关函数

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

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

1. 数组使用$[i] ，从0开始。
2. 对象使用$.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;

插入数据

1. 普通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');

2. 使用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")

查询数据

1. JSON_CONTAINS

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

   

2. 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
   

   

3. JSON_EXTRACT

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

   

4. JSON_UNQUOTE

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

   

5. COLUMN->PATH

   等同于JSON_EXTRACT

6. COLUMN->>PATH

   等同于JSON_UNQUOTE

7. 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;
   

   

   

条件查询

元数据

1. 查询某个path的值是否匹配

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

   

2. 查询某个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。

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