MySQL 8中 JSON 的使用

推荐用 MySQL 8.0.17 以上的版本，性能更好，同时也支持 Multi-Valued Indexes；

以下部分使用JSON_CONTAINS查询的地方说明：第二个参数只能为字符串，所以进行转换。因为参数需要json，json实际就是字符串

JSON ARRAY

字段的 创建索引、查询

创建表

CREATE TABLE `json_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `data` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入数据

INSERT INTO `json_test`(`data`) VALUES ('[1, 2, 3, 4]');
INSERT INTO `json_test`(`data`) VALUES ('[3,4]');
INSERT INTO `json_test`(`data`) VALUES ('[5,6,7]');
INSERT INTO `json_test`(`data`) VALUES ('[3,4,1]');
INSERT INTO `json_test`(`data`) VALUES ('[9,6,7]');
INSERT INTO `json_test`(`data`) VALUES ('[3, 4]');
INSERT INTO `json_test`(`data`) VALUES ('[10,5,6,7]');

创建索引

-- 给data 字段创建索引
CREATE INDEX idx_data ON json_test ( (CAST(data->'$' AS UNSIGNED ARRAY)) );

查询

EXPLAIN SELECT * FROM `json_test` WHERE JSON_CONTAINS(data, JSON_ARRAY(7));
EXPLAIN SELECT * FROM `json_test` WHERE JSON_CONTAINS(data, "7");

-- 只有下面的走索引（不走索引可能是mysql 进行优化了，不走索引比走索引快的情况。）
EXPLAIN SELECT * FROM `json_test` WHERE JSON_CONTAINS(data->'$', "7");
EXPLAIN SELECT * FROM `json_test` WHERE 7 MEMBER OF(data->'$')
EXPLAIN SELECT * FROM `json_test` WHERE JSON_CONTAINS(data->'$', "[5,7]"); -- 查询data中存在5和7的。并且关系
EXPLAIN SELECT * FROM `json_test` WHERE JSON_OVERLAPS(data->'$', "[5,7,2]");-- 查询data中存在5或者7或者1的。或关系
EXPLAIN SELECT * FROM `json_test` WHERE JSON_OVERLAPS(data->'$',JSON_ARRAY(5,7,2)); -- 同上，只是使用了函数来封装参数

JSON OBJECT

查询索引

json数组里面每个是json对象

-- 表名: json_test2
-- json字段：data
-- json 里面结构：[{"id":1,"name":"张三"}, {"id":2,"name":"王五"}]

查询、创建索引

CREATE TABLE `json_test2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `data` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `json_test2`(`id`, `data`) VALUES (1, '{\"id\": 2, \"name\": \"王五\"}');
INSERT INTO `json_test2`(`id`, `data`) VALUES (2, '{\"id\": 1, \"name\": \"张三\"}');

-- 创建索引
CREATE INDEX idx_data ON json_test2 ( (CAST(data->'$.id' AS UNSIGNED ARRAY)) );
-- 查询
EXPLAIN SELECT * FROM `json_test2` WHERE JSON_CONTAINS(data, JSON_OBJECT("id",2));
EXPLAIN SELECT * FROM `json_test2` WHERE JSON_CONTAINS(data, '{"id":2}');
SELECT data->'$.id' FROM `json_test2`;

-- 以下走索引
EXPLAIN SELECT * FROM `json_test2` WHERE JSON_CONTAINS(data->'$.id', "2"); -- -- 查询id为2的
EXPLAIN SELECT * FROM `json_test2` WHERE JSON_CONTAINS(data->'$.id', CONCAT(2,""));
EXPLAIN SELECT * FROM `json_test2` WHERE JSON_CONTAINS(data->'$.id', cast(2 as char));
EXPLAIN SELECT * FROM `json_test2` WHERE JSON_CONTAINS(data->'$.id', CONVERT(2, CHAR));

这里还可以创建虚拟列，查询，索引等等会方便很多

-- 新增个虚拟列。(虚拟列的数据不需要手动更新，也不能更新（报错），它是映射josn里面的对应值)
ALTER TABLE json_test2 ADD COLUMN u_id VARCHAR(255) AS (data->>"$.id");

-- 查询 就跟正常字段查询操作一样
SELECT id, u_id  FROM `json_test2` WHERE u_id = 2 
-- 可以给虚拟列创建索引，这里创建普通索引 （跟普通字段一样操作）
ALTER TABLE json_test2 ADD INDEX idx_uid(u_id);
-- 然后针对 u_id 字段操作就走索引

以上虚拟列操作，在navicat可视化里面可能看不到。直接在查询窗口或者命令行操作就行了。

说明:

以上所有 data->"$.id" data->'$' 等都是语法糖吧。$表示整个json对象

data->'$' 会转换为 JSON_EXTRACT(column, path)

data->>'$'会转换为 JSON_UNQUOTE( JSON_EXTRACT(column, path) )

"->>"表示去掉抽取结果的"号, 就是 函数JSON_UNQUOTE的作用

下面三种效果是一样的（验证上面说法）：

  • JSON_UNQUOTE( JSON_EXTRACT(column, path) )
  • JSON_UNQUOTE(column -> path)
  • column->>path
SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';

-- 查询对象a=1的 注意这里用JSON_OBJECT，数据类型要一样，这里a结果是数字类型，如果换成字符串则匹配不到
SELECT JSON_CONTAINS(@j, JSON_OBJECT("a",1));
SELECT JSON_CONTAINS(@j, '1', '$.a');

set @aa = '["x", "b", "45"]';

select JSON_CONTAINS(@aa, '"x"');

set @bb = '[1, 2, 3, 4]';
select JSON_CONTAINS(@bb, "2");
SELECT * FROM `json_test` WHERE JSON_CONTAINS(@bb, JSON_ARRAY(3))
SELECT * FROM `json_test` WHERE JSON_CONTAINS(@bb, "3")
//走索引                              
SELECT * FROM `json_test` WHERE JSON_CONTAINS(@bb->'$', "3")   

-- 例如：[3, {"a": [5, 6], "b": 10}, [99, 100]]
-- 下面演示取元素

set @abc = '[3, {"a": [5, 6], "b": 10}, [99, 100]]';

$[0] -- 3
select JSON_EXTRACT(@abc, "$[0]");

$[1] --  {"a": [5, 6], "b": 10}
$[2] -- [99, 100]
$[3] -- NULL
$[1].a -- [5, 6]
$[1].a[1]     -- 6
$[1].b    -- 10
$[2][0] -- 99

-- 数组里面是对象的情况； [{},{},{}]   
SELECT JSON_EXTRACT('[{"id":1, "name":"张三"}, {"id":2,"name":"李四"}]', '$[*].id');
-- 输出： [1, 2] 
CREATE INDEX idx_data ON json_test2 ( (CAST(data->'$[*].id' AS UNSIGNED ARRAY)) );

创建索引文档：https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued

参考资料：

文章目录