使用Mysql虚拟列(virtual columns)为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"}');

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