MySQL虛擬列的使用示例

更新時間：2024年11月11日 09:48:22 作者：xixingzhe2

虛擬列是MySQL中的一種特殊列,它不存儲在表中,而是在查詢時動態(tài)計算生成,虛擬列可以提高查詢效率、減少存儲需求、確保數(shù)據(jù)一致性、簡化查詢和保護敏感數(shù)據(jù),感興趣的可以了解一下

1、概述

在 MySQL 中，虛擬列（也稱為計算列或生成列）是一種特殊的列，它不存儲在數(shù)據(jù)庫表中，而是在查詢時動態(tài)計算生成。虛擬列可以基于表中的其他列進行計算，或者直接計算出一些值。它們在數(shù)據(jù)庫設計和查詢優(yōu)化中有一些重要的用途：

減少存儲需求：由于虛擬列不存儲在磁盤上，因此可以減少數(shù)據(jù)庫的存儲需求。
提高查詢效率：虛擬列在查詢時動態(tài)生成，可以減少對原始數(shù)據(jù)的計算，從而提高查詢效率。
數(shù)據(jù)一致性：虛擬列可以確保數(shù)據(jù)的一致性，因為它們總是基于表中其他列的最新值進行計算。
簡化查詢：虛擬列可以簡化復雜的 SQL 查詢，使得查詢更易于編寫和理解。
數(shù)據(jù)保護：虛擬列可以用于保護敏感數(shù)據(jù)，例如，通過計算列來顯示部分數(shù)據(jù)，而實際數(shù)據(jù)存儲在其他列中。

在 MySQL 中，虛擬列可以是存儲的（STORED）或非存儲的（VIRTUAL）。存儲的虛擬列實際上會存儲在表中，而非存儲的虛擬列則不會存儲，僅在查詢時計算。

創(chuàng)建虛擬列的語法

以下是創(chuàng)建虛擬列的基本語法：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    virtual_column AS (expression),
    ...
);

或者在現(xiàn)有的表中添加虛擬列：

ALTER TABLE table_name
ADD COLUMN virtual_column AS (expression);

2、示例

表結(jié)構(gòu)

假設有一個員工表 employees，包含員工的姓名、年齡和出生日期：

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `birth_date` date DEFAULT NULL,
  `full_name` varchar(255) GENERATED ALWAYS AS (concat(`name`,_utf8mb4' (',`age`,_utf8mb4' years old)')) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='員工';

在這個例子中，full_name 是一個虛擬列，它將員工的姓名和年齡結(jié)合起來生成一個完整的名稱。

初始化數(shù)據(jù)

INSERT INTO `employee` VALUES (1, 'Lois Simpson', 12, '2022-12-19', DEFAULT);
INSERT INTO `employee` VALUES (2, 'Nakagawa Sakura', 11, '2021-01-23', DEFAULT);
INSERT INTO `employee` VALUES (3, 'Xiang Rui', 10, '2013-04-04', DEFAULT);
INSERT INTO `employee` VALUES (4, 'Ono Momoe', 11, '2002-07-07', DEFAULT);
INSERT INTO `employee` VALUES (5, 'Fukuda Sara', 11, '2020-12-04', DEFAULT);
INSERT INTO `employee` VALUES (6, 'Ryan Dunn', 11, '2021-11-01', DEFAULT);
INSERT INTO `employee` VALUES (7, 'Ku Tsz Hin', 11, '2004-10-31', DEFAULT);
INSERT INTO `employee` VALUES (8, 'Todd Diaz', 11, '2023-05-11', DEFAULT);
INSERT INTO `employee` VALUES (9, 'Yamada Kenta', 11, '2015-06-15', DEFAULT);
INSERT INTO `employee` VALUES (10, 'Leslie Anderson', 5, '2010-08-19', DEFAULT);

查詢

注意事項

虛擬列的計算不能依賴于其他表的數(shù)據(jù)。
虛擬列不能用作存儲過程或觸發(fā)器的參數(shù)。
虛擬列不能用于外鍵約束。

通過這些功能，虛擬列在數(shù)據(jù)庫設計和優(yōu)化中提供了一種靈活且強大的工具。

3、優(yōu)缺點

優(yōu)點

數(shù)據(jù)一致性：
- 虛擬列的值總是基于表中其他列的當前值計算，確保數(shù)據(jù)的一致性。
減少存儲需求：
- 非存儲的虛擬列不需要額外的存儲空間，因為它們在查詢時動態(tài)計算。
簡化查詢：
- 通過將復雜的計算邏輯封裝在數(shù)據(jù)庫中，可以簡化應用層的代碼和查詢語句。
提高查詢效率：
- 對于存儲的虛擬列，如果這些列經(jīng)常被查詢使用，將它們存儲在數(shù)據(jù)庫中可以減少計算時間和提高查詢效率。
數(shù)據(jù)保護：
- 可以利用虛擬列顯示部分數(shù)據(jù)或進行數(shù)據(jù)格式化，從而保護敏感信息。
業(yè)務邏輯封裝：
- 將某些業(yè)務邏輯封裝在數(shù)據(jù)庫層面，減少應用層的計算負擔，有助于保持代碼的整潔和一致性。

缺點

增加計算負擔：
- 每次查詢時都需要重新計算非存儲的虛擬列，這可能會增加數(shù)據(jù)庫的計算負擔，特別是在高并發(fā)環(huán)境中。
限制使用：
- 虛擬列不能用作外鍵、存儲過程參數(shù)或觸發(fā)器參數(shù)，這限制了它們的使用場景。
索引復雜性：
- 雖然可以為存儲的虛擬列創(chuàng)建索引，但索引的維護可能會增加額外的復雜性和開銷。
存儲需求：
- 存儲的虛擬列需要額外的存儲空間，這可能會增加數(shù)據(jù)庫的存儲需求。
更新復雜性：
- 如果虛擬列依賴于其他列，當這些列被更新時，虛擬列的值也需要重新計算，這可能會影響更新操作的性能。
數(shù)據(jù)冗余：
- 存儲的虛擬列可能會引入數(shù)據(jù)冗余，尤其是在數(shù)據(jù)頻繁更新的情況下，這可能會導致數(shù)據(jù)不一致的風險。
限制靈活性：
- 虛擬列的計算邏輯在創(chuàng)建時固定，修改這些邏輯可能需要重新設計和調(diào)整數(shù)據(jù)庫結(jié)構(gòu)。