前言

在我們日常開發過程中，有時候因為對索引列進行函式呼叫，導致索引失效。舉個例子，比如我們要按月查詢記錄，而當我們 表中只存時間，如果我們使用如下語句，其中create_time為索引列

select count（*） from user where MONTH（create_time） = 5

雖然可能查到正確的結果，但透過explain我們會發現沒走索引。因此我們為了能確保使用索引，我們可能會改成

select count（*） from user where create_time BETWEEN ‘2022-05-01’ AND ‘2022-06-01’；

或者乾脆在資料庫表中冗餘一個月份的列欄位，並對這個月份建立索引。如果我們使用的mysql是5。7版本，我們則可以使用mysql5。7版本提供的一個新特性——

虛擬列

來達到上述效果

虛擬列

在mysql5。7支援2種虛擬列virtual columns 和 stored columns 。兩者的區別是virtual 只是在讀行的時候計算結果，但在物理上是不儲存，因此不佔儲存空間，且僅在InnoDB引擎上建二級索引，而stored 則是當行資料進行插入或更新時計算並存儲的，是需要佔用物理空間的，支援在MyISAM和InnoDB引擎建立索引

mysql5.7 預設的虛擬列型別為virtual columns

1、建立虛擬列語法

ALTER TABLE 表名稱 add column 虛擬列名稱 虛擬列型別 ［GENERATED ALWAYS］ as （表示式） ［VIRTUAL | STORED］；

2、使用虛擬列注意事項

a、衍生列的定義可以修改，但virtual和stored之間不能相互轉換，必要時需要刪除重建

b、虛擬列欄位只讀，不支援 INSRET 和 UPDATE

c、只能引用本表的非 generated column 欄位，不可以引用其它表的欄位

d、使用的表示式和運算子必須是 Immutable 屬性，比如不能使用 CONNECTION_ID（）， CURRENT_USER（）， NOW（）

e、可以將已存在的普通列轉化為stored型別的衍生列，但virtual型別不行；同樣的，可以將stored型別的衍生列轉化為普通列，但virtual型別的不行

f、虛擬列定義不允許使用自增 （AUTO_INCREMENT），也不允許使用自增基列

g、虛擬列允許修改表示式，但不允許修改儲存方式（只能透過刪除重新建立來修改）

h、如果虛擬列用作索引，會有一個缺點值會儲存兩次。一次用作虛擬列的值，一次用作索引中的值

3、虛擬列的使用場景

a、虛擬列可以簡化和統一查詢，將複雜條件定義為生成的列，可以在查詢時直接使用虛擬列（代替檢視）

b、儲存虛擬列可以用作例項化快取，以用於動態計算成本高昂的複雜條件

c、虛擬列可以模擬功能索引，並且可以使用索引，這對與無法直接使用索引的列（JSON 列）非常有用。

示例

因為mysql5。7也支援json列，因此本示例就以json和虛擬列為例子演示一下示例

1、建立示例表

CREATE TABLE `t_user_json` （ `id` int NOT NULL AUTO_INCREMENT， `user_info` json DEFAULT NULL， `create_time` datetime DEFAULT CURRENT_TIMESTAMP， PRIMARY KEY （`id`）） ENGINE=InnoDB DEFAULT CHARSET=utf8mb4；

2、建立虛擬列

注：

虛擬列可以在建表語句時候，直接建立即可。本示例是為了突出虛擬列語法

ALTER TABLE t_user_json ADD COLUMN v_user_name VARCHAR（50） GENERATED ALWAYS AS （JSON_UNQUOTE（json_extract（user_info，‘$。username’）））；

正常我們的json語句如下

{“age”： 23， “email”： “likairui@qq。com”， “mobile”： “89136682644”， “fullname”： “李凱瑞”， “username”： “likairui”}

我們透過JSON_UNQUOTE來去除雙引號，否則到時候生成的虛擬列v_user_name 的值會變成“likairui”，而實際我們需要的欄位值應該likairui

因為mysql5。7的json不是本文的重點，本文就不論述了，如果對mysql5。7 json語法函式感興趣的朋友可以檢視如下連結

https：//dev。mysql。com/doc/refman/5。7/en/json-functions。html

3、為虛擬列建立索引

ALTER TABLE t_user_json ADD INDEX idx_v_user_name（v_user_name）；

4、檢視生成的表資料

5、檢視是否使用了索引

EXPLAIN SELECT id，user_info，create_time，v_user_name AS username，v_date_month AS MONTH FROM t_user_json WHERE （v_user_name = ‘likairui’）

注：

在mysql8。0版本可以使用EXPLAIN ANALYZE，他可以檢視sql的耗時情況

EXPLAIN ANALYZE SELECT id，user_info，create_time，v_user_name AS username，v_date_month AS MONTH FROM t_user_json WHERE （v_user_name = ‘cengwen’）

6、程式碼層面的小細節

因為虛擬列是不能進行插入和更新的，因此使用orm框架的時候，要特別注意這點。比如使用mybatis-plus時，要記得在實體的虛擬列的對映欄位上加上如下註解

@TableField（value = “v_user_name”，insertStrategy = FieldStrategy。NEVER，updateStrategy = FieldStrategy。NEVER） private String username；

加上這個註解後，虛擬列欄位就不會進行更新或者插入

總結

本文基於mysql5。7大體介紹了一下虛擬列，如果是使用mysql8。0。13以上的版本，可以函式索引，他的實現方式本質也是基於虛擬列實現。所謂的函式索引就是在建立索引的時候，支援使用函式表示式。比如

ALTER TABLE user ADD INDEX（（MONTH（create_time）））；

透過函式索引也可以很方便提高我們的查詢效率。具體使用可以檢視如下連結

https：//dev。mysql。com/doc/refman/8。0/en/create-index。html

demo連結

https：//github。com/lyb-geek/springboot-learning/tree/master/springboot-mysql-virtual-column