使用Mysql虚拟列(virtual columns)为Json数据类型创建索引

最新推荐文章于 2025-03-01 15:27:43 发布

原创最新推荐文章于 2025-03-01 15:27:43 发布 · 1.8k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #json #索引 #数据库

MySQL学习心得专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何在MySQL中使用虚拟列(virtual columns)为JSON数据类型创建索引，包括构建虚拟列、创建索引及查询优化的方法，解决JSON字段查询效率低的问题。

使用MySQL虚拟列(virtual columns)为Json数据类型创建索引

创建表

CREATE TABLE user_info ( 
	uid INT auto_increment, 
	DATA json, 
	PRIMARY KEY(uid)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

插入数据

insert into user_info values (NULL,'{"name":"zhangsan","address":"beijing"}');
insert into user_info values (NULL,'{"name":"wanger","address":"tianjing"}');

构建姓名的虚拟列

方法1：
alter table user_info add v_name varchar(20) generated always as (JSON_EXTRACT(data,'$.name'));
方法2：
alter table user_info add v_name varchar(20) generated always as (data->'$.name');

构建索引

alter table user_info add index idx_name(v_name);

使用如下查询语句

SELECT * FROM user_info WHERE v_name='zhangsan';

在这里插入图片描述
并不能查询到结果，需使用

SELECT * FROM user_info WHERE v_name='"zhangsan"';

方可查询到结果。
在这里插入图片描述

由此可以知道构建的虚拟列中的数据多了 "" ,那么如何去掉呢

alter table user_info modify column v_name varchar(20) generated always as (JSON_UNQUOTE(data->'$.name')) VIRTUAL;

构建地址虚拟列并创建索引

构建虚拟列：
alter table user_info add v_address varchar(20) generated always as (JSON_UNQUOTE(data->'$.address'));
创建索引：
alter table user_info add index idx_user_info(v_name,v_address);

如此查询语句可以正常使用索引，查询与普通表无异。

此时的表结构由于多了v_name和v_address这两个虚拟列，再插入别的数据需要指明插入列 (不能给虚拟列插入数据)

insert into user_info(data) values ('{"name":"lisi","address":"sichuan"}');

需要注意，不能建立虚拟列与真实列的组合索引。