1. 介紹MySQL虛擬列

1.1 定義和作用

MySQL虛擬列是一種特殊的列，是mysql-5.7版本引入的一個新特性，它并不存儲數(shù)據(jù)，而是在查詢時動態(tài)生成數(shù)據(jù)。這種列的值通常是由其他列的值通過某種表達式計算得出的。虛擬列的主要作用是提高查詢效率和數(shù)據(jù)處理的便利性。它可以使我們在不增加實際存儲開銷的情況下，對數(shù)據(jù)進行更高效的查詢和管理。

1.2 虛擬列與普通列的區(qū)別

虛擬列與普通列在使用上有幾個主要的區(qū)別：

• 存儲方式：普通列的值在插入或更新時會被實際存儲在數(shù)據(jù)庫中，而虛擬列的值并不會被存儲，而是在查詢時動態(tài)計算生成。

• 更新方式：普通列的值可以直接通過UPDATE語句進行修改，而虛擬列的值則不能直接修改，它的值是由定義它的表達式?jīng)Q定的。

• 索引應(yīng)用：虛擬列可以被索引，這使得它在某些情況下可以提高查詢性能。雖然虛擬列的值在查詢時才生成，但是如果對虛擬列創(chuàng)建了索引，那么索引的值會被存儲，從而提高查詢效率。

雖然虛擬列在某些方面與普通列不同，但是在SQL查詢中，我們可以像使用普通列一樣使用虛擬列。

2. MySQL虛擬列的類型

在MySQL中，虛擬列主要分為兩種類型：生成列和存儲列。

2.1 生成列

生成列是一種特殊的虛擬列，它的值是由其他列的值通過一個表達式生成的。生成列的值不會被實際存儲，而是在查詢時動態(tài)計算生成。生成列可以是基于一個或多個列的任何MySQL合法的表達式。

語法如下

ALTER TABLE users
ADD COLUMN full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name));

2.2 存儲列

存儲列是生成列的一種特殊形式。與生成列不同，存儲列的值在插入或更新數(shù)據(jù)時會被計算并實際存儲在數(shù)據(jù)庫中。這意味著存儲列的值不需要在查詢時動態(tài)計算。

雖然存儲列需要占用額外的存儲空間，但是它可以提高查詢速度，因為它的值在查詢時已經(jīng)被計算并存儲好了。存儲列特別適用于那些計算成本高，但查詢頻繁的場景。

需要注意的是，雖然存儲列的值被存儲在數(shù)據(jù)庫中，但是它的值不能直接被修改，它的值仍然是由定義它的表達式?jīng)Q定的。

ALTER TABLE users
ADD COLUMN full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name)) STORED;

3. 如何創(chuàng)建和使用MySQL虛擬列

3.1 創(chuàng)建含有虛擬列的表

在創(chuàng)建新表時，你可以在表定義中包含一個或多個虛擬列。下面是一個例子：

CREATE TABLE employees (
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    full_name VARCHAR(200) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
);

在MySQL中，GENERATED ALWAYS是用來定義生成列（包括虛擬列和存儲列）的關(guān)鍵字。這個關(guān)鍵字指示MySQL，這個列的值不是由用戶直接插入或更新的，而是由一個表達式自動生成的。這個表達式可以引用表中的其他列的值。

3.2 更新虛擬列的值

虛擬列的值是由一個表達式計算出來的，這個表達式可以引用表中的其他列的值。因此，你不能直接更新虛擬列的值。相反，當(dāng)你更新虛擬列所依賴的列的值時，虛擬列的值會自動更新。

例如，假設(shè)你更新了一個員工的first_name：

UPDATE employees SET first_name = 'John' WHERE last_name = 'Doe';

在這個例子中，full_name列的值會自動更新，因為它是由first_name和last_name的值拼接而成的。

3.3 查詢虛擬列的值

虛擬列的值可以像普通列的值一樣查詢。例如，下面的查詢會返回所有員工的全名：

SELECT full_name FROM employees;

在這個例子中，full_name列的值是在查詢時計算的，而不是存儲在表中的。這意味著，每次你查詢full_name列的值時，MySQL都會重新計算這個值，但如果你使用的存儲的虛擬列則不會實時計算

4. MySQL虛擬列的使用場景

虛擬列在MySQL中有許多實用的應(yīng)用場景，包括優(yōu)化查詢、管理數(shù)據(jù)冗余、以及進行數(shù)據(jù)轉(zhuǎn)換和計算。以下是一些具體的使用例子：

4.1 聯(lián)合索引優(yōu)化

虛擬列可以用來創(chuàng)建聯(lián)合索引，以優(yōu)化查詢性能。例如，如果你經(jīng)常需要在first_name和last_name上進行聯(lián)合查詢，你可以創(chuàng)建一個虛擬列full_name，并在這個列上創(chuàng)建索引：

CREATE TABLE employees (
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    full_name VARCHAR(200) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
    INDEX (full_name)
);

在這個例子中，full_name列的索引可以用來優(yōu)化查詢，比如SELECT * FROM employees WHERE full_name = 'John Doe';。

4.2 數(shù)據(jù)冗余管理

虛擬列可以用來減少數(shù)據(jù)冗余。例如，如果你的表中有一列是由其他列的值計算出來的，你可以使用虛擬列，而不是存儲這個計算結(jié)果。這樣，你可以節(jié)省存儲空間，并確保數(shù)據(jù)的一致性。

例如，假設(shè)你有一個orders表，這個表有quantity和price兩列，你可以創(chuàng)建一個虛擬列total_price，它的值是quantity和price的乘積：

CREATE TABLE orders (
    quantity INT,
    price DECIMAL(10, 2),
    total_price DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * price) VIRTUAL
);

在這個例子中，total_price列的值會自動更新，當(dāng)quantity或price的值變化時。

4.3 數(shù)據(jù)轉(zhuǎn)換和計算

虛擬列可以用來進行數(shù)據(jù)轉(zhuǎn)換和計算。例如，你可以創(chuàng)建一個虛擬列來存儲日期的年份部分，或者計算兩列的比例。

例如，假設(shè)你有一個sales表，這個表有total_sales和total_costs兩列，你可以創(chuàng)建一個虛擬列profit_margin，它的值是total_sales和total_costs的比例：

CREATE TABLE sales (
    total_sales DECIMAL(10, 2),
    total_costs DECIMAL(10, 2),
    profit_margin DECIMAL(10, 2) GENERATED ALWAYS AS (total_sales / total_costs) VIRTUAL
);

在這個例子中，profit_margin列的值會自動更新，當(dāng)total_sales或total_costs的值變化時。

5. MySQL虛擬列的限制和注意事項

雖然虛擬列在許多情況下都非常有用，但是它們也有一些限制和注意事項。以下是一些主要的限制和注意事項：

5.1 數(shù)據(jù)類型限制

虛擬列的數(shù)據(jù)類型必須是可以從生成列表達式的結(jié)果類型推導(dǎo)出來的。例如，如果你的表達式是兩個整數(shù)列的乘積，那么虛擬列的數(shù)據(jù)類型應(yīng)該是整數(shù)或者是可以包含乘積結(jié)果的任何其他類型。

5.2 更新和刪除限制

虛擬列的值是由表達式計算出來的，不能直接更新。如果你嘗試直接更新虛擬列的值，MySQL將會返回一個錯誤。同樣，你也不能刪除虛擬列，除非你同時刪除依賴于該列的所有其他對象，如索引和觸發(fā)器。

5.3 其他注意事項

• 虛擬列的表達式不能引用其他虛擬列的值。
• 虛擬列的表達式不能包含不確定的元素，比如當(dāng)前時間或者隨機數(shù)。
• 虛擬列不能有默認值。
• 虛擬列的值在查詢時計算，因此，如果虛擬列的表達式非常復(fù)雜，或者表中的數(shù)據(jù)量非常大，查詢虛擬列的值可能會比查詢存儲的列的值慢。
• 雖然虛擬列不占用存儲空間，但是在虛擬列上創(chuàng)建的索引會占用存儲空間。

在使用虛擬列時，需要考慮到這些限制和注意事項，以確保你的數(shù)據(jù)庫設(shè)計和查詢能夠正確、有效地工作。

6. 實戰(zhàn)：使用MySQL虛擬列解決實際問題

6.1 問題描述

假設(shè)我們正在開發(fā)一個電子商務(wù)網(wǎng)站，我們有一個products表，這個表包含了產(chǎn)品的price和discount信息?，F(xiàn)在，我們希望能夠快速查詢出打折后的價格，但我們不希望在表中為每個產(chǎn)品都存儲一個打折后的價格字段，因為這會增加數(shù)據(jù)冗余，并且當(dāng)price或discount發(fā)生變化時，需要手動更新打折后的價格。

6.2 解決方案設(shè)計

我們可以使用MySQL的虛擬列來解決這個問題。我們可以在products表中添加一個虛擬列discounted_price，這個列的值是price和discount的乘積。由于虛擬列的值是動態(tài)計算的，因此當(dāng)price或discount發(fā)生變化時，discounted_price的值會自動更新。

6.3 實現(xiàn)步驟

ALTER TABLE products 
ADD COLUMN discounted_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * (1 - discount/100)) VIRTUAL;

在這個SQL語句中，我們添加了一個名為discounted_price的虛擬列，它的值是price和discount的乘積。注意，我們假設(shè)discount是一個百分比值，比如15表示15%的折扣。

6.4 結(jié)果和效果分析

現(xiàn)在，我們可以直接查詢discounted_price列來獲取打折后的價格，而不需要在應(yīng)用程序中進行計算。這使得查詢更加簡單和直觀。同時，由于discounted_price列的值是動態(tài)計算的，因此當(dāng)price或discount發(fā)生變化時，我們不需要手動更新打折后的價格，減少了數(shù)據(jù)冗余和維護工作。

需要注意的是，雖然虛擬列不占用存儲空間，但是如果虛擬列的表達式非常復(fù)雜，或者表中的數(shù)據(jù)量非常大，查詢虛擬列的值可能會比查詢存儲的列的值慢。因此，在使用虛擬列時，需要根據(jù)實際情況進行權(quán)衡。

到此這篇關(guān)于Mysql虛擬列的實現(xiàn)示例的文章就介紹到這了,更多相關(guān)Mysql虛擬列內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論