柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) Mysql虛擬列

http://yzkb.51969.com/

文章目錄

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

2. MySQL虛擬列的類型2.1 生成列2.2 存儲(chǔ)列

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

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

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

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

1. 介紹MySQL虛擬列

1.1 定義和作用

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

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

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

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

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

2. MySQL虛擬列的類型

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

2.1 生成列

生成列是一種特殊的虛擬列，它的值是由其他列的值通過(guò)一個(gè)表達(dá)式生成的。生成列的值不會(huì)被實(shí)際存儲(chǔ)，而是在查詢時(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ǔ)列的值不需要在查詢時(shí)動(dòng)態(tài)計(jì)算。

雖然存儲(chǔ)列需要占用額外的存儲(chǔ)空間，但是它可以提高查詢速度，因?yàn)樗闹翟诓樵儠r(shí)已經(jīng)被計(jì)算并存儲(chǔ)好了。存儲(chǔ)列特別適用于那些計(jì)算成本高，但查詢頻繁的場(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)你更新虛擬列所依賴的列的值時(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)樗怯蒮irst_name和last_name的值拼接而成的。

3.3 查詢虛擬列的值

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

SELECT full_name FROM employees;

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

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

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

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

虛擬列可以用來(lái)創(chuàng)建聯(lián)合索引，以優(yōu)化查詢性能。例如，如果你經(jīng)常需要在first_name和last_name上進(jìn)行聯(liá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)化查詢，比如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ù)類型限制

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

5.2 更新和刪除限制

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

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

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

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

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)在，我們希望能夠快速查詢出打折后的價(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)在，我們可以直接查詢discounted_price列來(lái)獲取打折后的價(jià)格，而不需要在應(yīng)用程序中進(jìn)行計(jì)算。這使得查詢更加簡(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ù)量非常大，查詢虛擬列的值可能會(huì)比查詢存儲(chǔ)的列的值慢。因此，在使用虛擬列時(shí)，需要根據(jù)實(shí)際情況進(jìn)行權(quán)衡。

柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) Mysql虛擬列

http://yzkb.51969.com/

好文鏈接