【这一篇就够】MySQL创建JSON数据的索引

耐心阅读，会有收获的

〇. 从创建索引的要素说起

提示：数据版本：MySQL 8 +

首先需要知道，创建json数据字段的索引和普通字段的索引，在本质上没有区别。

为什么没什么区别？我们思考一下，创建一个索引的要素有哪些？我们需要提供什么信息？

1. 表名，即要知道你要操作哪张表。
2. 待创建索引的字段，毕竟索引是根据一个或多个字段来创建的。
3. 待创建索引字段的数据类型。
4. 待创建的索引名称, 需要唯一。

普通数据类型的字段, 如：int, char, unsigned等，我们很好提供上面的信息，例如：为student表的name CHAR(32)字段创建一个索引，语句如下：

ALTER TABLE  `student` 
	ADD INDEX student_name_index(`name`);

该语句中，student提供了1. 表名, name同时提供2. 待创建索引的字段，即student.name和3.待创建索引字段的数据类型, 即CHAR(32)两种信息，student_name_index提供了4. 待创建的索引名称。拥有了这4个信息，我们可成功创建索引。

但可惜的是，MySQL不支持直接为json数据类型创建索引，因此执行下方的语句将报错。

ALTER TABLE  `student` 
	ADD INDEX student_name_index(`json_field`);

因此，MySQL8引入了间接创建的方式。也就是，可以间接指定json数据的成员或者元素作为创建索引的字段，而字段对应的数据类型我们手动指定。

换句话说，就是为需要创建索引的json数据成员，提供2. 待创建索引的字段和3.待创建索引字段的数据类型两种信息，从而拥有创建索引的4要素, 即可创建索引。

如何提供？语法如下所示：

ALTER TABLE `student` 
	ADD INDEX student_name_index(【有所区别，需要做改动的地方】);

其中，【有所区别，需要做改动的地方】的部分，为与创建普通字段的索引有所区别的部分，可见，本质上和创建普通索引的方式几乎没有区别。

接下来将围绕着这一区别，对MySQL创建json索引的方式进行细致的讲解。

一. 创建json索引

由于json有两类不同的数据形式，即：json对象（如：{“id”: 1, “name”:”he”}），json数组（如：[“1″,”2″,”3”]），接下来分开进行讲解。

1. 当字段的数据为json对象

例如：有student表，拥有一个json类型的字段data，数据形如：{“id”: 1, “name”:”he”}

创建索引语法如下：

ALTER Table `【表名】` ADD INDEX 【索引名】(
  (CAST(`【表中json的字段名】`-> "$.【需要建立索引的数据成员名】" 
  as 【需要建立索引的数据成员的数据类型】 ARRAY))
);

其中，【需要建立索引的数据成员的数据类型】自行参考数据MySQL8拥有的数据类型，例如可填：CHAR(32), UNSIGNED等。

1.1 示例：为data字段数据中的name成员创建索引。

创建索引语句如下：

ALTER Table `student` ADD INDEX student_name_index(  
	(CAST( `data` -> "$.name"  as CHAR(32) ARRAY))
);

2. 当数据为json数组

例如: 例如：有student表，拥有一个json类型的字段data，数据形如：[“1″,”2”]或[{“id”: 1, “name”:”he”},{“id”: 2, “name”:”zhi”}]

创建索引语法如下：

ALTER Table `【表名】` ADD INDEX 【索引名】(
    (CAST(`【表中json的字段名】` -> "$[*].【需要作为索引的json数据的成员名】" 
    as 【成员名对应的数据类型】 ARRAY))
);

其中， $[*]代表数据中的每个元素，也就是将会让所有的元素加入索引，例如：第N行记录的数据为[“1″,”2”]，此时创建够的索引会将”1″和”2″元素都映射到第N行。若为$[0],那么只会将每行的第一个元素加入索引，例如：第N行的第一个元素”1″。

此外，【成员名对应的数据类型】自行参考数据mysql拥有的数据类型，例如：CHAR(32), UNSIGNED等。

2.1 示例：当data字段数据为[“1″,”2”]，为数组中所有元素创建索引。

创建索引语句：

ALTER Table `student` ADD INDEX student_data_index(  
	(CAST( `data` -> "$[*]"  as `CHAR(32)` ARRAY))
);

2.2 示例：当data字段数据为[{“id”: 1, “name”:”he”},{“id”: 2, “name”:”zhi”}]，为数组中所有元素的成员id创建索引。

创建索引语句：

ALTER Table `student` ADD INDEX student_data_index(
	(CAST( `data` -> "$[*].id"  as UNSIGNED ARRAY))
);

3. 当数据为json数组，但内部元素不为json对象

例如：[“1″,”2”, “3”]就是为json数组，但内部元素不为json对象的数据，使用上述的1.和2.的语法都可，推荐使用2.语义更为明确。下方的示例，展示这种特殊的情况。

二. 完整示例（内部元素不为json对象的json数组）

1. 表定义和数据准备

-- 创建表
DROP TABLE IF EXISTS `test`;
CREATE TABLE test ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    jchararr JSON NOT NULL COMMENT "元素为char类型的json数组"
);

-- 插入数据
INSERT INTO 
    test (jchararr) 
VALUES 
    ('["1","2","3"]'),
    ('["1"]'),
    ('[]'),
    ('["4","2","3"]');

2. 为 jchararr 字段中的每个元素创建索引

接下来将展示两种不同的方式。

2.1 方式1

2.1.1 创建索引

ALTER Table test ADD INDEX jchararr_index(
     /* 由于数组中的元素类型，MySQL不知道，
     故需要手动指定数组中的元素的类型为Char*/
    ( CAST(`jchararr` as CHAR(32) ARRAY) )
);

2.1.2 触发索引的查询方式

SELECT * FROM test WHERE '2' MEMBER OF(jchararr);

2.2.3 性能分析

EXPLAIN SELECT * FROM test WHERE '2' MEMBER OF(jchararr);

运行结果：

2.1.4 【方式1】的局限性

只适用于当字段的数据为[1,2,3,4,4]或者[‘a’,’b’,’c’]的json数组，而若是[{“id”: 1},{“d”: 2′}]这样的数据，需创建id的索引，并不支持，因为仅通过CAST(jchararr as CHAR(32) ARRAY)是无法让mysql知道[{“id”: 1},{“d”: 2′}]中有id这个成员和它的数据类型。当然方式2可以通过$[*].member_namejson内部成员访问符，进行指定，从而解决该问题。

2.2 方式2

2.2.1 创建索引

ALTER Table test ADD INDEX jchararr_index_2(
     /* 由于数组中的元素类型，MySQL不知道，
     故需要手动指定数组中的元素的类型为Char*/
    ( CAST(`jchararr`->'$[*]' as CHAR(32) ARRAY) )
);

上述的$[*]代表json数组中的所有元素，若元素类型为json对象，如：[{“id”: 1},{“d”: 2′}]，则需改成$[*].id。

2.2.2 触发索引的查询方式

SELECT * FROM test WHERE '2' MEMBER OF(jchararr->'$[*]');

上述的$[*]代表json数组中的所有元素，若元素类型为json对象，如：[{“id”: 1},{“d”: 2′}]，则需改成$[*].id。

2.2.3 性能分析

EXPLAIN SELECT * FROM test WHERE '2' MEMBER OF(jchararr->'$[*]');

运行结果：

总结

总的来说就3步：

1. 通过MySQL的语法定位到JSON数据中需要创建索引的成员，例如：

# json数组对象中的id需要创建索引
`data` -> "$[*].id"

2. 对该成员指定MySQL的数据类型，例如：

# 将id属性映射为UNSIGNED类型，构建索引
CAST( `data` -> "$[*].id"  as UNSIGNED ARRAY)

3. 最后创建索引，例如：

ALTER Table `student` ADD INDEX `student_data_index`( 
	(CAST( `data` -> "$[*].id"  as `UNSIGNED` ARRAY))
);

花了一整天写的文章，如果对你有帮助，希望能够点赞或收藏一下，谢谢支持。