json 插入数据_使用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"}');
           

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