MySQL虚拟列技术

参考文章：https://blog.csdn.net/weixin_42531779/article/details/113592374

 

 1、查看mysql版本，需要大于5.7版本以上

select VERSION();

 

 

2、创建表

CREATE TABLE user_info (
uid INT(11) NOT NULL AUTO_INCREMENT,
uname VARCHAR(20) NOT NULL DEFAULT '',
other_info json, 
PRIMARY KEY(uid)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

 

3、插入json数据两种方式

INSERT INTO user_info (uname,other_info) VALUES ('first user',JSON_OBJECT("age",22,"real_name","Wayne","sex","男"));
INSERT INTO user_info (uname,other_info) VALUES ('second user','{"age":21, "real_name":"Jone", "sex":"女", "height":170, "weight":48}');

 

JSON_OBJECT函数

 

4、查询数据的方式两种方式

select * from user_info where other_info->'$.age'=22 and other_info->'$.real_name'='Wayne'
select * from user_info where json_extract(other_info,'$.age')=21 and other_info->'$.weight'=48;

 

5、新增虚拟列，自动映射对应值

ALTER TABLE user_info ADD COLUMN v_age TINYINT(3) UNSIGNED GENERATED ALWAYS AS (other_info->'$.age');

ALTER TABLE user_info ADD INDEX v_age(v_age);

 

UPDATE user_info SET other_info=json_set(other_info,'$.age','24') WHERE uid=1;

explain select * from user_info where v_age=24;