快捷導(dǎo)航

MySQL虛擬列的具體使用

更新時(shí)間：2024年09月04日 11:02:23 作者：黑風(fēng)風(fēng)

虛擬列是一種特殊類型的表列,它不像普通列直接存儲(chǔ)數(shù)據(jù),而是根據(jù)其他列中的數(shù)據(jù)動(dòng)態(tài)生成,本文主要介紹了MySQL虛擬列的具體使用,感興趣的可以了解一下

在MySQL中，虛擬列（也稱為生成列）是一種特殊類型的表列，它不像普通列直接存儲(chǔ)數(shù)據(jù)，而是根據(jù)其他列中的數(shù)據(jù)動(dòng)態(tài)生成。虛擬列可以基于一個(gè)或多個(gè)其他列的值進(jìn)行計(jì)算，計(jì)算結(jié)果即為虛擬列的值。虛擬列可以是持久的也可以是非持久的，它們的主要作用是簡(jiǎn)化查詢，優(yōu)化性能，以及維持?jǐn)?shù)據(jù)一致性。

虛擬列的類型

1. 生成列的基本概念

生成列分為兩種類型：存儲(chǔ)的生成列和虛擬的生成列。存儲(chǔ)的生成列（STORED GENERATED COLUMN）會(huì)在物理存儲(chǔ)上占用空間，而虛擬的生成列（VIRTUAL GENERATED COLUMN）在每次查詢時(shí)動(dòng)態(tài)計(jì)算，不占用物理存儲(chǔ)空間。

2. 存儲(chǔ)的生成列

存儲(chǔ)的生成列會(huì)將計(jì)算結(jié)果存儲(chǔ)在數(shù)據(jù)庫中，這意味著每次對(duì)依賴列進(jìn)行更新后，生成列的值也會(huì)相應(yīng)更新并存儲(chǔ)。這種列的優(yōu)點(diǎn)是加快了查詢速度，因?yàn)椴恍枰看尾樵儠r(shí)都進(jìn)行計(jì)算。但缺點(diǎn)是增加了存儲(chǔ)開銷，并可能影響插入和更新的性能。

3. 虛擬的生成列

與存儲(chǔ)的生成列相對(duì)，虛擬的生成列不會(huì)存儲(chǔ)計(jì)算結(jié)果，每次查詢時(shí)都會(huì)動(dòng)態(tài)計(jì)算。這減少了存儲(chǔ)開銷，但可能會(huì)導(dǎo)致查詢性能略有下降，特別是在涉及大量計(jì)算的場(chǎng)景中。

虛擬列的應(yīng)用場(chǎng)景

1. 簡(jiǎn)化查詢

通過在虛擬列中預(yù)先定義計(jì)算公式，可以簡(jiǎn)化復(fù)雜的SQL查詢語句。例如，如果經(jīng)常需要計(jì)算銷售總額（商品數(shù)量乘以單價(jià)），可以直接創(chuàng)建一個(gè)虛擬列來存儲(chǔ)這個(gè)計(jì)算結(jié)果。

假設(shè)有一個(gè)銷售數(shù)據(jù)表，其中包含商品價(jià)格 (price) 和銷售數(shù)量 (quantity) 兩個(gè)列。經(jīng)常需要計(jì)算銷售總額，即每一條記錄的 price * quantity。如果每次查詢都寫這個(gè)計(jì)算式，不僅增加了查詢的復(fù)雜性，還可能導(dǎo)致性能下降。引入一個(gè)虛擬列 total_sales，該列直接存儲(chǔ) price * quantity 的計(jì)算結(jié)果，可以極大簡(jiǎn)化查詢：

CREATE TABLE sales (
    product_id INT,
    price DECIMAL(10,2),
    quantity INT,
    total_sales DECIMAL(10,2) AS (price * quantity) VIRTUAL
);

現(xiàn)在，每次需要獲取銷售總額時(shí)，只需直接查詢 total_sales 列。

2. 優(yōu)化查詢性能

虛擬列特別適合用于索引。雖然MySQL不允許直接對(duì)非持久的虛擬列創(chuàng)建索引，但可以對(duì)持久的虛擬列創(chuàng)建索引，這樣可以顯著提高基于這些計(jì)算結(jié)果的查詢速度。

在上述銷售表中，如果需要經(jīng)常根據(jù)銷售總額進(jìn)行查詢或排序，可以將虛擬列 total_sales 設(shè)為持久化，并對(duì)其建立索引：

CREATE TABLE sales (
    product_id INT,
    price DECIMAL(10,2),
    quantity INT,
    total_sales DECIMAL(10,2) AS (price * quantity) STORED
);

CREATE INDEX idx_total_sales ON sales(total_sales);

這樣，利用索引，基于銷售總額的查詢和排序操作將顯著提高效率，特別是在數(shù)據(jù)量較大時(shí)。

3. 維護(hù)數(shù)據(jù)一致性

虛擬列確保了數(shù)據(jù)的動(dòng)態(tài)一致性，因?yàn)樗鼈兊闹凳腔谄渌袆?dòng)態(tài)生成的。這有助于在數(shù)據(jù)庫層面維護(hù)數(shù)據(jù)一致性，避免了應(yīng)用層進(jìn)行多余的數(shù)據(jù)處理。

考慮一個(gè)員工表，其中有員工的出生日期 (birth_date) 和年齡 (age) 兩個(gè)列。虛擬列可以用來實(shí)時(shí)計(jì)算年齡，確保年齡數(shù)據(jù)始終準(zhǔn)確反映當(dāng)前日期相對(duì)于出生日期的差異：

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    birth_date DATE,
    age INT AS (TIMESTAMPDIFF(YEAR, birth_date, CURDATE())) VIRTUAL
);

這樣，age 列會(huì)動(dòng)態(tài)地根據(jù)當(dāng)前日期和出生日期計(jì)算員工的年齡，無需手動(dòng)更新。

創(chuàng)建和使用虛擬列

1. 創(chuàng)建虛擬列

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

CREATE TABLE my_table (
    column_a INT,
    column_b INT,
    total_c INT AS (column_a + column_b) VIRTUAL
);

2. 使用虛擬列

一旦虛擬列被創(chuàng)建，你可以像使用普通列一樣使用它們，包括在SELECT語句中引用它們，或者在WHERE和ORDER BY子句中使用它們。

繼續(xù)使用上面的銷售表，查詢某個(gè)產(chǎn)品的銷售記錄，包括計(jì)算得到的總銷售額：

SELECT product_id, price, quantity, total_sales
FROM sales
WHERE product_id = 101;

這里的 total_sales 是虛擬列，但在查詢中它的使用與普通列無異。使用虛擬列，使得查詢更加直觀和簡(jiǎn)潔。

虛擬列表達(dá)式的規(guī)則

在MySQL中使用虛擬列時(shí)，需要定義一個(gè)表達(dá)式來指定如何計(jì)算列的值。這些表達(dá)式需要遵守一些特定的規(guī)則和限制，以確保它們能正確地在數(shù)據(jù)庫中執(zhí)行。以下是創(chuàng)建和使用虛擬列時(shí)必須遵循的幾個(gè)關(guān)鍵規(guī)則：

1. 表達(dá)式的確定性

虛擬列的表達(dá)式必須是“確定性的”，意味著給定相同的輸入，表達(dá)式必須產(chǎn)生相同的輸出。這確保了無論何時(shí)何地計(jì)算虛擬列，其結(jié)果都是一致的。例如，表達(dá)式不應(yīng)該包含任何隨機(jī)數(shù)生成或調(diào)用非確定性函數(shù)。

2. 參考列的限制

虛擬列的表達(dá)式只能使用同一張表中的其他列作為參考。不能引用其他表的列或進(jìn)行跨表查詢。此外，表達(dá)式中引用的列必須在虛擬列聲明之前在表定義中出現(xiàn)。

3. 子查詢的禁止

虛擬列的表達(dá)式不能包含子查詢。這是因?yàn)樽硬樵兛赡苌婕按罅繑?shù)據(jù)處理或外部表的數(shù)據(jù)，這可能會(huì)復(fù)雜化虛擬列的計(jì)算和維護(hù)。

4. 存儲(chǔ)函數(shù)和過程的禁用

虛擬列不能調(diào)用任何存儲(chǔ)過程或存儲(chǔ)函數(shù)。這是為了避免虛擬列的值依賴于可能改變的外部環(huán)境或數(shù)據(jù)庫狀態(tài)，保持計(jì)算的純粹性和高效性。

5. 限制使用的數(shù)據(jù)類型

虛擬列的表達(dá)式中使用的數(shù)據(jù)類型應(yīng)該是標(biāo)準(zhǔn)的SQL數(shù)據(jù)類型，并且要確保表達(dá)式結(jié)果的數(shù)據(jù)類型與虛擬列定義的數(shù)據(jù)類型兼容。例如，如果虛擬列被定義為整數(shù)類型，那么表達(dá)式也應(yīng)該產(chǎn)生整數(shù)類型的結(jié)果。

6. 性能考量

雖然虛擬列不存儲(chǔ)物理數(shù)據(jù)，但復(fù)雜的計(jì)算表達(dá)式可能會(huì)在查詢時(shí)增加計(jì)算負(fù)擔(dān)。建議使用盡可能簡(jiǎn)單的表達(dá)式，特別是對(duì)于頻繁查詢的虛擬列。

遵守這些規(guī)則可以幫助開發(fā)者有效地利用MySQL的虛擬列功能，同時(shí)保持?jǐn)?shù)據(jù)庫的性能和一致性。在設(shè)計(jì)數(shù)據(jù)庫和查詢時(shí)，應(yīng)謹(jǐn)慎選擇是否使用虛擬列，以及如何定義它們的計(jì)算表達(dá)式。