前言

最近在做訂單系統重構時，遇到了一個有趣的問題。

系統里有很多地方都要計算訂單的總價（數量×單價），這個計算邏輯分散在各個服務中，產生了不少相似甚至重復的代碼。

代碼評審時，同事提出了一個建議 —— 使用MySQL的虛擬列來統一處理這類計算，在調研后我們一致決定采納。

經過一段時間的生產實踐，也逐漸摸清了虛擬列的套路，這里就和大家分享一下這個特性的使用心得。

耐心看完，你一定有所收獲。

MySQL 5.7版本引入的虛擬列（Generated Columns）允許我們定義一個基于其他列計算得出的列。

它有點像Excel中的計算列，但更加強大和靈活。

虛擬列分為兩種類型：

1. VIRTUAL（虛擬列）：在讀取時實時計算，不占用存儲空間
2. STORED（存儲列）：在數據寫入時計算并存儲，會占用實際存儲空間

基本語法

創建虛擬列的語法不難：

-- 例如，計算訂單總價CREATE TABLE orders (quantity INT,unit_price DECIMAL(10,2),total_price DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) VIRTUAL);

虛擬列還是比較好用的，但是也得注意，不是所有的場景都適合使用，這里先講適合的。

適合使用虛擬列的場景

1. 可以簡化查詢和業務操作的，避免在多個查詢中重復編寫相同的表達式

-- 例如，計算訂單總價CREATE TABLE orders (quantity INT,unit_price DECIMAL(10,2),total_price DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) VIRTUAL);

2. 需要確保計算值始終正確且一致

-- 自動計算年齡CREATE TABLE persons (birth_date DATE,age INT GENERATED ALWAYS AS (YEAR(CURDATE()) - YEAR(birth_date)) VIRTUAL);

3. 從JSON列中提取特定字段或者做一些判斷

CREATE TABLE products (attributes JSON,product_name VARCHAR(100) GENERATED ALWAYS AS (attributes->>'$.name') VIRTUAL
);

4. 利用虛擬列創建索引，提高查詢性能

這個特性需要MySQL 8.0+

CREATE TABLE users (full_name VARCHAR(100),first_name VARCHAR(50) GENERATED ALWAYS AS (SUBSTRING_INDEX(full_name, ' ', 1)) VIRTUAL,INDEX (first_name));

不適合使用虛擬列的場景

1. 計算成本過高、過于復雜的表達式會影響性能
2. 頻繁更新的表，則不適用VIRTUAL列，畢竟在每次查詢時都會重新計算
3. 存儲空間嚴重受限時，謹慎使用使用STORED類型時，因為STORED列會增加存儲空間
4. MySQL 5.7 以下不支持虛擬列
5. 計算依賴于不確定性函數（如NOW()、RAND()）的，也不能使用

虛擬列的限制

1. 不能在虛擬列中使用子查詢
2. 不能引用自增列
3. 不能使用存儲函數或用戶定義的函數
4. 不能使用不確定函數（如NOW()）
5. 不能將虛擬列作為外鍵
6. 不能設置默認值

性能考慮

1. VIRTUAL列：查詢時計算，適合計算簡單、不頻繁查詢的列
2. STORED列：寫入時計算，適合計算復雜、頻繁查詢的列
3. 索引：只能在STORED列上創建索引（MySQL 8.0+支持在VIRTUAL列上創建索引）

總結

MySQL虛擬列為我們提供了一個優雅的解決方案，可以將部分計算邏輯從應用層轉移到數據層，既保證了數據的一致性，又簡化了應用層代碼。

在我們的訂單系統中，目前有不少字段都用上了虛擬列，確實代碼會更加清晰，維護成本也有相應降低。

當然，是否使用虛擬列需要根據具體場景來判斷。

對于簡單的計算邏輯，特別是需要在多處使用的計算結果，虛擬列是個不錯的選擇。
但對于復雜的業務邏輯，還是建議放在應用層處理為好。

希望這篇文章能幫助大家更好地理解和使用MySQL虛擬列這個實用的特性。

如果你的項目中有類似的場景，不妨嘗試一下這個功能，也歡迎使用過的朋友來交流下心得。