柚子快報邀請碼778899分享：數(shù)據(jù)庫 Mysql虛擬列

http://yzkb.51969.com/

文章目錄

1. 介紹MySQL虛擬列1.1 定義和作用1.2 虛擬列與普通列的區(qū)別

2. MySQL虛擬列的類型2.1 生成列2.2 存儲列

3. 如何創(chuàng)建和使用MySQL虛擬列3.1 創(chuàng)建含有虛擬列的表3.2 更新虛擬列的值3.3 查詢虛擬列的值

4. MySQL虛擬列的使用場景4.1 聯(lián)合索引優(yōu)化4.2 數(shù)據(jù)冗余管理4.3 數(shù)據(jù)轉(zhuǎn)換和計算

5. MySQL虛擬列的限制和注意事項5.1 數(shù)據(jù)類型限制5.2 更新和刪除限制5.3 其他注意事項

6. 實戰(zhàn)：使用MySQL虛擬列解決實際問題6.1 問題描述6.2 解決方案設(shè)計6.3 實現(xiàn)步驟6.4 結(jié)果和效果分析

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ù)。虛擬列不能有默認(rèn)值。虛擬列的值在查詢時計算，因此，如果虛擬列的表達式非常復(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)衡。

柚子快報邀請碼778899分享：數(shù)據(jù)庫 Mysql虛擬列

http://yzkb.51969.com/

好文鏈接