1. 介紹MySQL虛擬列

1.1 定義和作用

MySQL虛擬列是一種特殊的列，是mysql-5.7版本引入的一個(gè)新特性，它并不存儲(chǔ)數(shù)據(jù)，而是在查詢(xún)時(shí)動(dòng)態(tài)生成數(shù)據(jù)。這種列的值通常是由其他列的值通過(guò)某種表達(dá)式計(jì)算得出的。虛擬列的主要作用是提高查詢(xún)效率和數(shù)據(jù)處理的便利性。它可以使我們?cè)诓辉黾訉?shí)際存儲(chǔ)開(kāi)銷(xiāo)的情況下，對(duì)數(shù)據(jù)進(jìn)行更高效的查詢(xún)和管理。

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

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

• 存儲(chǔ)方式：普通列的值在插入或更新時(shí)會(huì)被實(shí)際存儲(chǔ)在數(shù)據(jù)庫(kù)中，而虛擬列的值并不會(huì)被存儲(chǔ)，而是在查詢(xún)時(shí)動(dòng)態(tài)計(jì)算生成。
• 更新方式：普通列的值可以直接通過(guò)UPDATE語(yǔ)句進(jìn)行修改，而虛擬列的值則不能直接修改，它的值是由定義它的表達(dá)式?jīng)Q定的。
• 索引應(yīng)用：虛擬列可以被索引，這使得它在某些情況下可以提高查詢(xún)性能。雖然虛擬列的值在查詢(xún)時(shí)才生成，但是如果對(duì)虛擬列創(chuàng)建了索引，那么索引的值會(huì)被存儲(chǔ)，從而提高查詢(xún)效率。

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

2. MySQL虛擬列的類(lèi)型

在MySQL中，虛擬列主要分為兩種類(lèi)型：生成列和存儲(chǔ)列。

2.1 生成列

生成列是一種特殊的虛擬列，它的值是由其他列的值通過(guò)一個(gè)表達(dá)式生成的。生成列的值不會(huì)被實(shí)際存儲(chǔ)，而是在查詢(xún)時(shí)動(dòng)態(tài)計(jì)算生成。生成列可以是基于一個(gè)或多個(gè)列的任何MySQL合法的表達(dá)式。

語(yǔ)法如下

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

2.2 存儲(chǔ)列

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

雖然存儲(chǔ)列需要占用額外的存儲(chǔ)空間，但是它可以提高查詢(xún)速度，因?yàn)樗闹翟诓樵?xún)時(shí)已經(jīng)被計(jì)算并存儲(chǔ)好了。存儲(chǔ)列特別適用于那些計(jì)算成本高，但查詢(xún)頻繁的場(chǎng)景。

需要注意的是，雖然存儲(chǔ)列的值被存儲(chǔ)在數(shù)據(jù)庫(kù)中，但是它的值不能直接被修改，它的值仍然是由定義它的表達(dá)式?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)建新表時(shí)，你可以在表定義中包含一個(gè)或多個(gè)虛擬列。下面是一個(gè)例子：

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

3.2 更新虛擬列的值

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

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

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

在這個(gè)例子中，full_name列的值會(huì)自動(dòng)更新，因?yàn)樗怯?code>first_name和last_name的值拼接而成的。

3.3 查詢(xún)虛擬列的值

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

SELECT full_name FROM employees;

在這個(gè)例子中，full_name列的值是在查詢(xún)時(shí)計(jì)算的，而不是存儲(chǔ)在表中的。這意味著，每次你查詢(xún)full_name列的值時(shí)，MySQL都會(huì)重新計(jì)算這個(gè)值，但如果你使用的存儲(chǔ)的虛擬列則不會(huì)實(shí)時(shí)計(jì)算

4. MySQL虛擬列的使用場(chǎng)景

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

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

虛擬列可以用來(lái)創(chuàng)建聯(lián)合索引，以?xún)?yōu)化查詢(xún)性能。例如，如果你經(jīng)常需要在first_name和last_name上進(jìn)行聯(lián)合查詢(xún)，你可以創(chuàng)建一個(gè)虛擬列full_name，并在這個(gè)列上創(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)
);

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

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

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

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

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

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

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

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

例如，假設(shè)你有一個(gè)sales表，這個(gè)表有total_sales和total_costs兩列，你可以創(chuàng)建一個(gè)虛擬列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
);

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

5. MySQL虛擬列的限制和注意事項(xiàng)

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

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

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

5.2 更新和刪除限制

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

5.3 其他注意事項(xiàng)

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

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

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

6.1 問(wèn)題描述

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

6.2 解決方案設(shè)計(jì)

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

6.3 實(shí)現(xiàn)步驟

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

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

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

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

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

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

最新評(píng)論