MySQL JSON检索

最新推荐文章于 2025-05-28 14:58:38 发布

原创最新推荐文章于 2025-05-28 14:58:38 发布 · 1.4k 阅读

CC 4.0 BY-SA版权

本文介绍了从MySQL5.7开始支持的JSON格式数据类型，并通过一个简单的测试展示了如何在JSON列上创建索引。在JSON列上进行检索时，需要为检索的key创建虚拟列并建立索引，以避免全表扫描。通过这种方式，MySQL实现了一种空间换时间的策略，将JSON数据冗余存储到虚拟列中，提高查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL5.7版本开始支持JSON格式，在创建表时，可以指定列表的数据类型为JSON，但是如何在JSON格式上创建索引呢？？

本人做了一个简单测试。

第一步：建立一个包含JSON类型的表：

CREATE TABLE `json_test`
 (

  `id` int(8) NOT NULL AUTO_INCREMENT,

  `content`
 json NOT NULL,

  PRIMARY KEY (`id`)

)
 ENGINE=InnoDB DEFAULT CHARSET=utf8;

第二步：初始化数据

insert into json_test(content)
 value('{"name":"zhangsan","age":18}');

insert into json_test(content)
 value('{"name":"lisi","age":19}');

insert into json_test(content)
 value('{"name":"wangwu","age":20}');

第三步：查询JSON类列的数据

select json_extract(content, '$.name') from json_test where json_extract(content, '$.name')="zhangsan";

通过expain分析改查询语句，发现其走全表扫描

在网上查询资料，得知如果要在JSON列上进行检索，需要对检索的key创建虚拟列，然后再虚拟列上创建索引

第四步：在content列上，对"name"建立虚拟列

ALTER TABLE json_test ADD name_virtual varchar(32)
 GENERATED ALWAYS AS (json_extract(content, '$.name'))
 VIRTUAL;

第五步：对虚拟列创建索引

CREATE INDEX name_virtual_index ON json_test(name_virtual);

再次做查询（注，where条件需要使用虚拟列来进行检索，如果直接用JSON列比较，还是会走全表扫描）

explain select json_extract(content, '$.name') from json_test where name_virtual="zhangsan"\G

总结：

其实MySQL通过一种空间换时间的做法，类似创建一个触发器，把JSON列上的数据冗余存储到虚拟列上，比较的时候通过走虚拟列的索引，再定位到实际数据。