MySQL數(shù)據(jù)庫(kù)必會(huì)技能，虛擬列的用法

對(duì)于想要將自動(dòng)生成的數(shù)據(jù)添加到表中的任何人來(lái)說(shuō)， MySQL 虛擬列 是一個(gè)強(qiáng)大、易于使用和高級(jí)的功能。

大邑縣網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián)公司,大邑縣網(wǎng)站設(shè)計(jì)制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為大邑縣上1000+提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\成都外貿(mào)網(wǎng)站建設(shè)公司要多少錢，請(qǐng)找那個(gè)售后服務(wù)好的大邑縣做網(wǎng)站的公司定做！

INSERT 生成的列允許您在不使用and UPDATE 子句的情況下將自動(dòng)生成的數(shù)據(jù)存儲(chǔ)在表中。 這個(gè)有用的特性自 5.7 版 起就已成為 MySQL 的一部分，它代表了在生成數(shù)據(jù)時(shí)觸發(fā)器的另一種方法。此外，生成的列可以幫助您更輕松、更高效地查詢。

虛擬列 列類似于普通列，但您不能手動(dòng)更改其值。這是因?yàn)楸磉_(dá)式定義了如何根據(jù)從同一行的其他列中讀取的其他值來(lái)生成生成列的值。因此，生成的列在表的域內(nèi)工作，其定義不能涉及 JOIN 語(yǔ)句。

換句話說(shuō)，您可以將生成的列視為一種視圖，但僅限于列。請(qǐng)注意，生成的列與 SQL 觸發(fā)器 不同，您只能在使用 CREATE TABLE or語(yǔ)句時(shí)定義它們，語(yǔ)法如下： ALTER TABLE

該 AS (generated_column_expression) 子句指定要添加或更新到表中的列是生成的列。定義 MySQL 將用于計(jì)算列值的 generation_expression 表達(dá)式，它不能引用另一個(gè)生成的列或除當(dāng)前表的列之外的任何內(nèi)容。另外，請(qǐng)注意生成表達(dá)式只能涉及不可變函數(shù)。例如，您不能在生成的列表達(dá)式定義中使用返回當(dāng)前日期的函數(shù)，因?yàn)樗且粋€(gè)可變函數(shù)。

您還可以在關(guān)鍵字前面 AS 加上 GENERATED ALWAYS 關(guān)鍵字以使生成的列的性質(zhì)更加明確，但這是可選的。然后，您可以指示生成列的類型是 VIRTUAL 還是 STORED 。您將在下面的章節(jié)中了解這兩種類型之間的區(qū)別。默認(rèn)情況下，如果沒(méi)有在查詢中明確指定，MySQL 會(huì)將生成的列標(biāo)記為 VIRTUAL .

現(xiàn)在讓我們看看生成的列語(yǔ)法在 CREATE TABLE 查詢中的作用：

在此示例中，該 full_name 列將自動(dòng)存儲(chǔ) first_name 和 last_name 列的連接。

如前所述，您可以將生成的列定義為 VIRTUAL 或 STORED。現(xiàn)在讓我們仔細(xì)看看這兩種類型。

MySQL 不存儲(chǔ)標(biāo)記為 VIRTUAL 的 虛擬列 。這意味著 MySQL 在需要時(shí)動(dòng)態(tài)評(píng)估其值。 BEFORE 這通常在觸發(fā)任何查詢后立即發(fā)生。換句話說(shuō)，虛擬生成的列不占用存儲(chǔ)空間。

MySQL 存儲(chǔ)任何生成的標(biāo)記為 STORED 的列。這意味著每次插入或更新行時(shí)，MySQL 都會(huì)評(píng)估其值并將其存儲(chǔ)在磁盤上。換句話說(shuō)，存儲(chǔ)列需要存儲(chǔ)空間，就好像它是普通列一樣。

現(xiàn)在讓我們進(jìn)一步了解虛擬列和存儲(chǔ)生成列的優(yōu)缺點(diǎn)。

優(yōu)點(diǎn)

缺點(diǎn)

優(yōu)點(diǎn)

缺點(diǎn)

采用生成的列有幾個(gè)原因，但以下三個(gè)是最重要的。

如您所見(jiàn)，您可以通過(guò)將四列與以下生成的列聚合來(lái)輕松生成此數(shù)據(jù)字段：

這將產(chǎn)生：

在這種情況下，生成的列使您能夠直接在數(shù)據(jù)庫(kù)級(jí)別標(biāo)準(zhǔn)化數(shù)據(jù)字段格式。此外，存儲(chǔ)生成的列避免了每次需要時(shí)都構(gòu)造此字段的不可避免的開(kāi)銷。

通常，您使用網(wǎng)站 URL 中的資源 ID 或REST API來(lái)檢索您需要的數(shù)據(jù)。但是公開(kāi)暴露您的 ID 可能會(huì)帶來(lái)安全問(wèn)題。當(dāng)您發(fā)現(xiàn)自己使用自動(dòng)增量 ID 時(shí)尤其如此，這很容易預(yù)測(cè)并使抓取或機(jī)器人攻擊更容易。

為避免這種情況，您可以考慮通過(guò)使用自動(dòng)生成的、隨機(jī)的、更安全的公共 ID 來(lái)隱藏您的原始 ID。您可以通過(guò)對(duì)您的 ID 進(jìn)行散列處理，使用虛擬生成的列來(lái)實(shí)現(xiàn)這一點(diǎn)，如下所示：

請(qǐng)注意，為避免生成已知的哈希值，您可以將您的 ID 與特殊關(guān)鍵字連接起來(lái)。 在此處了解有關(guān) MySQL 加密和壓縮功能的更多信息。

過(guò)濾數(shù)據(jù)時(shí)，有些列比其他列更有用。此外，您通常必須更改存儲(chǔ)在列中的值的表示形式，以使過(guò)濾更簡(jiǎn)單或更直觀。您可以定義一個(gè)有用的生成列來(lái)存儲(chǔ)以所需格式執(zhí)行過(guò)濾所需的信息，而不是在每個(gè)過(guò)濾器查詢中執(zhí)行此操作。

例如，您可以定義一個(gè)生成的列，以便更輕松地找到籃球隊(duì)中的球員，如下所示：

這樣的列將產(chǎn)生：

如前所述，您只能在表中使用生成的列。此外，它們只能涉及不可變函數(shù)，并且MySQL 生成它們的值以響應(yīng) INSERT or UPDATE 查詢。另一方面，觸發(fā)器是 MySQL 自動(dòng)執(zhí)行的存儲(chǔ)程序，每當(dāng)與特定表關(guān)聯(lián)的 或 事件發(fā)生 INSERT 時(shí) UPDATE 。 DELETE 換句話說(shuō)，觸發(fā)器可以涉及多個(gè)表和所有 MySQL 函數(shù)。與生成的列相比，這使它們成為更完整的解決方案。同時(shí)，MySQL 觸發(fā)器本質(zhì)上使用和定義更復(fù)雜，也比生成的列慢。

mysql怎么將兩個(gè)表查詢出來(lái)的結(jié)果再去關(guān)聯(lián)下一張表？

可以用兩表的查詢結(jié)果集做為一個(gè)虛擬表（為其取一個(gè)表別名），然后再用該虛擬表與另一張表實(shí)施連接查詢即可。請(qǐng)參考以下例子：

假設(shè)有三張表

1）商品表（商品ID，商品名稱）

2）入庫(kù)表（商品ID，入庫(kù)數(shù)量，入庫(kù)時(shí)間）

3）出庫(kù)表（商品ID，出庫(kù)數(shù)量，出庫(kù)時(shí)間）

要求列出所有商品名稱、商品ID及其當(dāng)前庫(kù)存余額

SQL查詢語(yǔ)句如下：

select 商品表.商品名稱,商品表.商品ID,b.庫(kù)存余額

from 商品表 left join

(select a.商品ID,sum(a.數(shù)量）as 庫(kù)存余額 from

(select 商品ID,入庫(kù)數(shù)量 as 數(shù)量 from 入庫(kù)表 union all

select 商品ID,-1*出庫(kù)數(shù)量 from 出庫(kù)表) a group by a.商品ID) b

on 商品表.商品ID=b.商品ID;

-- 說(shuō)明上述語(yǔ)句中的“a”和“b”分別是兩個(gè)中間過(guò)渡查詢結(jié)果集取的表別名，也就所謂的虛擬表。

如何查看mysql消耗的邏輯讀和物理讀情況

對(duì)于查詢處理，可將其分為邏輯查詢處理和物理查詢處理。邏輯查詢處理表示執(zhí)行查詢應(yīng)該產(chǎn)生什么樣的結(jié)果，而物理查詢代表MySQL數(shù)據(jù)庫(kù)是如何得到結(jié)果的。

邏輯查詢處理

MySQL真正的執(zhí)行順序如下：

(8)SELECT (9)DISTINCTselect_list

(1)FROM left_table

(3)join_typeJOINright_table

(2) ON join_condition

(4)WHERE where_condition

(5)GROUP BY group_by_list

(6)WITH {CUBE|ROLLUP}

(7)HAVINGhaving_condition

(10)ORDER BYorder_by_list

(11)LIMIT limit_number

FORM：對(duì)FORM子句中的左表 left_table 和右表 right_table 執(zhí)行笛卡兒積，產(chǎn)生虛擬表VT1。

ON:對(duì)虛擬表VT1應(yīng)用ON篩選，只有那些符合 join_condition 的行才被插入到虛擬表VT2中。

JOIN：如果指定了OUTER JOIN，那么保留表中未匹配的行作為外部行添加到虛擬表VT2中，產(chǎn)生虛擬表VT3。如果FROM字句包含兩個(gè)以上表，則對(duì)上一個(gè)連接生成的結(jié)果表VT3和下一個(gè)表重復(fù)執(zhí)行步驟1~步驟3，直到處理完所有的表為止。

WHERE：對(duì)虛擬表VT3應(yīng)用WHERE過(guò)濾條件，只有符合 where_condition 的記錄才被插入到虛擬表VT4中。

GROUP BY:根據(jù)GROUP BY子句中的列，對(duì)VT4中的記錄進(jìn)行分組操作，產(chǎn)生虛擬表VT5.

CUBE|ROLLUP:對(duì)虛擬表VT5進(jìn)行 CUBE或ROLLUP，產(chǎn)生VT6.

HAVING:對(duì)虛擬表VT6應(yīng)用HAVING過(guò)濾器，只有符合 having_condition 的記錄才能被插入虛擬表VT7.

SELECT:第二次執(zhí)行SELECT操作，選擇指定的咧，插入到虛擬表VT8。

DISTINCT：去除重復(fù)數(shù)據(jù)，產(chǎn)生虛擬表VT9。

ORDER BY:將虛擬表VT9中的記錄按照 order_by_list 進(jìn)行排序操作，產(chǎn)生虛擬表VT10。

LIMIT：取出指定行的記錄，產(chǎn)生虛擬表VT11，并返回給查詢用戶。

物理查詢處理

上面是邏輯查詢處理，但是數(shù)據(jù)庫(kù)也許并不會(huì)按照邏輯查詢處理的方式進(jìn)行查詢。MySQL數(shù)據(jù)庫(kù)層有Parser和Optimizer兩個(gè)組件。Parser的工作就是分析SQL語(yǔ)句，而Optimizer的工作就是對(duì)這個(gè)SQL語(yǔ)句進(jìn)行優(yōu)化，選擇一條最優(yōu)的路徑來(lái)選取數(shù)據(jù)，但是必須保證物理查詢處理的最終結(jié)果和邏輯查詢處理的結(jié)果是相等的。

如果表上建有索引，那么優(yōu)化器就會(huì)判斷SQL語(yǔ)句是否可以利用該索引來(lái)進(jìn)行優(yōu)化。如果沒(méi)有可以利用的索引，可能整個(gè)SQL語(yǔ)句的執(zhí)行代價(jià)非常大。

假設(shè)表A和表B都是有10萬(wàn)行數(shù)據(jù)，并且兩個(gè)表都沒(méi)有索引，因此最終SQL解析器解析的執(zhí)行結(jié)果為邏輯處理的步驟，共經(jīng)過(guò)11個(gè)步驟來(lái)進(jìn)行數(shù)據(jù)的查詢。最終根據(jù)笛卡爾積生成一張?zhí)摫鞻T1，共100億行數(shù)據(jù)，執(zhí)行這條SQL語(yǔ)句一般的電腦至少得跑一個(gè)多小時(shí)。

然而，如果這時(shí)候表B上添加一個(gè)主鍵值，在執(zhí)行這條SQL語(yǔ)句，那么只需話費(fèi)1秒。促使這個(gè)查詢時(shí)間大幅減少的原因就是添加索引后避免了笛卡兒積表的產(chǎn)生。

物理查詢會(huì)根據(jù)索引來(lái)進(jìn)行優(yōu)化。

mysql多表查詢并創(chuàng)建視圖

MySQL在多個(gè)數(shù)據(jù)表上創(chuàng)建視圖

在MySQL中，使用CREATE VIEW語(yǔ)句也可以在兩個(gè)或兩個(gè)以上的數(shù)據(jù)表上創(chuàng)建視圖。

實(shí)例

在department表和worker表上創(chuàng)建一個(gè)名為worker_view1的視圖。

1. 創(chuàng)建視圖的代碼如下：

CREATE ALGORITHM=MERGE VIEW

worker_view1(name,department,sex,age,location)

AS SELECT name,department.d_name,sex,birthday,address

FROM worker,department WHERE worker.d_id=department.d_id

WITH LOCAL CHECK OPTION;

在DOS提示符窗口中查看MySQL在多個(gè)數(shù)據(jù)表上創(chuàng)建視圖的操作效果。如下圖所示：

在DOS提示符窗口中查看MySQL在多個(gè)數(shù)據(jù)表上創(chuàng)建視圖的操作效果

從上圖中可以看出，代碼的執(zhí)行結(jié)果顯示視圖創(chuàng)建成功。

2. 使用DESC語(yǔ)句查詢虛擬表的結(jié)構(gòu)。如下圖所示：

使用DESC語(yǔ)句查詢虛擬表的結(jié)構(gòu)

從上圖中可以看出，視圖worker_view1的屬性分別為name、department、sex、age和location。

視圖指定的屬性列表對(duì)應(yīng)著兩個(gè)不同的表的屬性列（字段）。

視圖的屬性名與屬性列表中的屬性名相同。

該實(shí)例中的SELECT語(yǔ)句查詢出了department表的d_name字段，還有worker表的name、sex、birthday和address。其中，department表的d_name字段對(duì)應(yīng)視圖的department字段；worker表的birthday字段對(duì)應(yīng)視圖的age字段。

同時(shí)，視圖worker_view1的ALGORITHM的值指定為MERGE。還增加了WITH LOCAL CHECK OPTION約束。

本示例說(shuō)明，視圖可以將多個(gè)表上的操作簡(jiǎn)潔的表示出來(lái)。

提示

同時(shí)在多個(gè)表上創(chuàng)建視圖是非常有用的。比如，系統(tǒng)中有student表、department表、score表和grade表，分別存儲(chǔ)學(xué)生的信息、院系信息、課程信息和成績(jī)信息?？梢栽谶@4個(gè)表上創(chuàng)建一個(gè)視圖，用來(lái)顯示學(xué)生姓名、學(xué)號(hào)、班級(jí)、院系、所選課程和課程成績(jī)。