快捷導(dǎo)航

MySQL虛擬列的具體使用

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

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

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

虛擬列的類型

1. 生成列的基本概念

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

2. 存儲的生成列

存儲的生成列會將計算結(jié)果存儲在數(shù)據(jù)庫中，這意味著每次對依賴列進行更新后，生成列的值也會相應(yīng)更新并存儲。這種列的優(yōu)點是加快了查詢速度，因為不需要每次查詢時都進行計算。但缺點是增加了存儲開銷，并可能影響插入和更新的性能。

3. 虛擬的生成列

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

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

1. 簡化查詢

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

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

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

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

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

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

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

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ù)量較大時。

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

虛擬列確保了數(shù)據(jù)的動態(tài)一致性，因為它們的值是基于其他列動態(tài)生成的。這有助于在數(shù)據(jù)庫層面維護數(shù)據(jù)一致性，避免了應(yīng)用層進行多余的數(shù)據(jù)處理。

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

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

這樣，age 列會動態(tài)地根據(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ù)使用上面的銷售表，查詢某個產(chǎn)品的銷售記錄，包括計算得到的總銷售額：

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

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

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

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

1. 表達式的確定性

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

2. 參考列的限制

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

3. 子查詢的禁止

虛擬列的表達式不能包含子查詢。這是因為子查詢可能涉及大量數(shù)據(jù)處理或外部表的數(shù)據(jù)，這可能會復(fù)雜化虛擬列的計算和維護。

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

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

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

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