MySQL 允许你在 JSON 数据上创建索引
测试用例
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`courses` json DEFAULT NULL,
`address` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO student (name, age, courses, address) VALUES
('张伟', 20, '[{"credits": 3.0, "course_id": "CS101", "course_name": "计算机基础"}, {"credits": 4.0, "course_id": "MATH202", "course_name": "高等数学"}]', '{"zip": "100000", "city": "北京", "state": "北京市", "street": "北京市朝阳区幸福路123号"}'),
('李娜', 22, '[{"credits": 3.0, "course_id": "BIO301", "course_name": "生物学基础"}]', '{"zip": "200000", "city": "上海", "state": "上海市", "street": "上海市浦东新区花园路456号"}');
mysql> select * from student\G;
*************************** 1. row ***************************
id: 1
name: 张伟
age: 20
courses: [{"credits": 3.0, "course_id": "CS101", "course_name": "计算机基础"}, {"credits": 4.0, "course_id": "MATH202", "course_name":
"高等数学"}]address: {"zip": "100000", "city": "北京", "state": "北京市", "street": "北京市朝阳区幸福路123号"}
*************************** 2. row ***************************
id: 2
name: 李娜
age: 22
courses: [{"credits": 3.0, "course_id": "BIO301", "course_name": "生物学基础"}]
address: {"zip": "200000", "city": "上海", "state": "上海市", "street": "上海市浦东新区花园路456号"}
2 rows in set (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
需求:
在student表的courses字段中,为JSON数据内的course_id键创建索引。
mysql> -- 可以看到已经用到索引
mysql> EXPLAIN SELECT * FROM student
-> WHERE JSON_CONTAINS(courses->'$[*].course_id', '"CS101"')\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: range
possible_keys: idx_course_id
key: idx_course_id
key_len: 203
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
上述的方式是通过MySQL 8.0 创建函数索引解决。
那么 MySQL 5.7 如何在 JSON 数据上创建索引?
答:使用虚拟列+全文索引:
mysql> -- 使用全文搜索
mysql> EXPLAIN SELECT * FROM student
-> WHERE MATCH(course_ids) AGAINST ('CS101' IN BOOLEAN MODE)\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: fulltext
possible_keys: idx_course_ids
key: idx_course_ids
key_len: 0
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Ft_hints: no_ranking
1 row in set, 1 warning (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
结论
JSON 数据类型是在 MySQL 中存储 JSON 数据的好方法。 它提供验证功能,允许创建索引,并使用 JSON 函数操作 JSON 数据。 它是 TEXT 数据类型的最佳替代品。