1. 介紹MySQL虛擬列

1.1 定義和作用

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

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

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

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

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

• 索引應用：虛擬列可以被索引，這使得它在某些情況下可以提高查詢性能。雖然虛擬列的值在查詢時才生成，但是如果對虛擬列創(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 存儲列

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

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

需要注意的是，雖然存儲列的值被存儲在數據庫中，但是它的值不能直接被修改，它的值仍然是由定義它的表達式決定的。

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是用來定義生成列（包括虛擬列和存儲列）的關鍵字。這個關鍵字指示MySQL，這個列的值不是由用戶直接插入或更新的，而是由一個表達式自動生成的。這個表達式可以引用表中的其他列的值。

3.2 更新虛擬列的值

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

例如，假設你更新了一個員工的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ōu)化查詢、管理數據冗余、以及進行數據轉換和計算。以下是一些具體的使用例子：

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

虛擬列可以用來創(chuàng)建聯合索引，以優(yōu)化查詢性能。例如，如果你經常需要在first_name和last_name上進行聯合查詢，你可以創(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 數據冗余管理

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

例如，假設你有一個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列的值會自動更新，當quantity或price的值變化時。

4.3 數據轉換和計算

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

例如，假設你有一個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列的值會自動更新，當total_sales或total_costs的值變化時。

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

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

5.1 數據類型限制

虛擬列的數據類型必須是可以從生成列表達式的結果類型推導出來的。例如，如果你的表達式是兩個整數列的乘積，那么虛擬列的數據類型應該是整數或者是可以包含乘積結果的任何其他類型。

5.2 更新和刪除限制

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

5.3 其他注意事項

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

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

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

6.1 問題描述

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

6.2 解決方案設計

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

6.3 實現步驟

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

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

6.4 結果和效果分析

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

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

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

最新評論