mysql 5.7中有很多新的特性，但平時可能很少用到，這里列舉2個實用的功能：虛擬列及json字段類型

一、先創建一個測試表：

drop table if exists t_people;

CREATE TABLE t_people(

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` varchar(50) NOT NULL DEFAULT '',

`profile` json not null ,

`created_at` TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),

`updated_at` TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),

PRIMARY KEY (id));

注：這里profile是一個json類型的字段，另db編碼采用utf8mb4

二、生成測試數據

delimiter //

-- 寫一段存儲過程，方便后面生成測試數據

create procedure batchInsert()

begin

declare i int;

declare v_name varchar(50);

declare v_profile varchar(100);

set i=0;

while i<100000 do

set v_name = concat(substring('趙錢孫李周吳鄭王張楊',floor(1+(rand()*10)),1),substring('菩提樹下的楊過',floor(1+(rand()*7)),1),substring('我愛北京天安門',floor(1+(rand()*7)),1),i);

set v_profile = concat("{\"phone\":\"",concat('13',floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9))) , "\",\"age\":",i,"}");

insert into t_people(`name`,profile) values(v_name,v_profile);

set i=i+1;

end while;

end; //

注：這段存儲過程不是本文重點，看不懂的同學不用深研，大概意思就是name隨機生成，profile隨機生成一個類似{"phone":"13xxxxxx","age":x}的內容。

調用一下這個存儲過程，生成100000條測試數據，數據大致長下面這樣：

需求來了，假如我們要查姓“張”的人有多少個？

這顯然是一個全表掃描！

三、前綴索引

肯定有同學想到了，在name上建一個前綴索引，只對name的第1個字做索引

alter table t_people add key ix_name(name(1));

確實是個好辦法，效果也不錯

但是需求總是變化的，如果想查第2個字是“楊”的人有多少？

依然會全表掃描。

四、虛擬列

alter table t_people add second_name varchar(3) generated always as(substring(name,2,1)) stored;

創建了一個虛擬列second_name，其值是substring(name,2,1)，即name中的第2個字，最后的stored表示，數據寫入時這個列的值就會計算(詳情可參考最后的參考鏈接)

注：虛擬列并不是真正的列，insert時也無法指定字段值。

然后在這個列上創建索引：

alter table t_people add index ix_second_name(`second_name`);

再來看下執行計劃，索引生效了，掃描行數也明顯下降。

當然，sql語句也可以改成：

explain select count(0) from t_people where second_name='楊';

這樣看上去更直觀，效果不變。

五、json檢索

又來新需求了：要查profile中手機號為13589135467，并且姓“吳”的人

注意：profile->"$.phone"=xxx 就是json字段的檢索語法

分析執行計劃，可以看到前綴索引“ix_name”生效了，但還有優化空間，仍然可以借助虛擬列，創建2個虛擬列phone、first_name，并創建聯合索引。

alter table t_people add first_name varchar(3) generated always as(substring(name,1,1)) stored;

alter table t_people add phone varchar(20) generated always as(profile->"$.phone") stored;

alter table t_people add index ix_phone_firstname(phone,first_name);

加了這2個虛擬列后，數據長這樣：

注：phone列提取出來后，前后會帶上引號。

剛才的需求，可以改寫sql:

select * from t_people where phone='\"13589135467\"' and name like '吳%';

最后看下執行計劃：

掃描行數下降到個位數，效果十分明顯。

參考文章：

總結

以上是生活随笔為你收集整理的mysql修改虚拟列属性失败_mysql虚拟列(Generated Columns)及JSON字段类型的使用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。