快捷導(dǎo)航

MySQL虛擬列的使用示例

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

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

1、概述

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

減少存儲(chǔ)需求：由于虛擬列不存儲(chǔ)在磁盤上，因此可以減少數(shù)據(jù)庫(kù)的存儲(chǔ)需求。
提高查詢效率：虛擬列在查詢時(shí)動(dòng)態(tài)生成，可以減少對(duì)原始數(shù)據(jù)的計(jì)算，從而提高查詢效率。
數(shù)據(jù)一致性：虛擬列可以確保數(shù)據(jù)的一致性，因?yàn)樗鼈兛偸腔诒碇衅渌械淖钚轮颠M(jìn)行計(jì)算。
簡(jiǎn)化查詢：虛擬列可以簡(jiǎn)化復(fù)雜的 SQL 查詢，使得查詢更易于編寫和理解。
數(shù)據(jù)保護(hù)：虛擬列可以用于保護(hù)敏感數(shù)據(jù)，例如，通過計(jì)算列來顯示部分?jǐn)?shù)據(jù)，而實(shí)際數(shù)據(jù)存儲(chǔ)在其他列中。

在 MySQL 中，虛擬列可以是存儲(chǔ)的（STORED）或非存儲(chǔ)的（VIRTUAL）。存儲(chǔ)的虛擬列實(shí)際上會(huì)存儲(chǔ)在表中，而非存儲(chǔ)的虛擬列則不會(huì)存儲(chǔ)，僅在查詢時(shí)計(jì)算。

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

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

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)

假設(shè)有一個(gè)員工表 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='員工';

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

初始化數(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);

查詢

注意事項(xiàng)

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

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

3、優(yōu)缺點(diǎn)

優(yōu)點(diǎn)

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

缺點(diǎn)

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