mysql虛擬列怎么用 mysql虛擬表的作用

MySQL之虛擬列

從MySQL 5.7開始，支持兩種Generated Column，即Virtual Generated Column和Stored Generated Column;

吳江網(wǎng)站制作公司哪家好，找成都創(chuàng)新互聯(lián)！從網(wǎng)頁設(shè)計、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、自適應(yīng)網(wǎng)站建設(shè)等網(wǎng)站項目制作，到程序開發(fā)，運營維護。成都創(chuàng)新互聯(lián)自2013年創(chuàng)立以來到現(xiàn)在10年的時間，我們擁有了豐富的建站經(jīng)驗和運維經(jīng)驗，來保證我們的工作的順利進行。專注于網(wǎng)站建設(shè)就選成都創(chuàng)新互聯(lián)。

CREATE TABLE t1 (c1 INT);

ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);

1、主鍵索引不能包含virtual generated column 如：

mysql create table t(a int, b int , c int as (a / b), primary key(c));

ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.

2、Virtual Generated Column不能作為外鍵

3、不能使用非確定函數(shù)，如：

mysql alter table a ADD p3 DATE GENERATED ALWAYS AS (curtime()) virtual;

ERROR 3763 (HY000): Expression of generated column 'p3' contains a disallowed function: curtime.

4、無法刪除源列，如：

mysql alter table t100w drop k1;

ERROR 3108 (HY000): Column 'k1' has a generated column dependency.

5、非法數(shù)據(jù)，如：

mysql create table t( x int, y int, z int as( x / 0));

Query OK, 0 rows affected (0.22 sec)

mysql insert into t(x,y) values(1,1);

ERROR 1365 (22012): Division by 0

測試sql:

SELECT test_vv FROM t100w limit 10000; #虛擬列無索引

虛擬列函數(shù)：

(concat( k1 ,_utf8mb4'-', k2 ))

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

對于想要將自動生成的數(shù)據(jù)添加到表中的任何人來說， MySQL 虛擬列 是一個強大、易于使用和高級的功能。

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

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

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

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

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

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

在此示例中，該 full_name 列將自動存儲 first_name 和 last_name 列的連接。

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

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

MySQL 存儲任何生成的標記為 STORED 的列。這意味著每次插入或更新行時，MySQL 都會評估其值并將其存儲在磁盤上。換句話說，存儲列需要存儲空間，就好像它是普通列一樣。

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

優(yōu)點

缺點

優(yōu)點

缺點

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

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

這將產(chǎn)生：

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

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

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

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

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

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

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

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

關(guān)于mysql 虛擬列 什么意思

函數(shù)會阻止索引，建立虛擬列存放函數(shù)計算結(jié)果，再對虛擬列進行索引。（MySQL5.7特性）

例如：

添加虛擬列：

ALTER?TABLE?union_member?ADD?COLUMN?virtual_col_date

date?GENERATED?ALWAYS?AS

(adddate(gmt_modified,INTERVAL?18?YEAR))?VIRTUAL;

查看虛擬列：

desc?union_member;

給虛擬列添加索引：

CREATE?INDEX?idx_virtual_col_date?on?union_member(virtual_col_date);

查看索引：

show?index?from?union_member;

優(yōu)化體系--詳解MySQL 5.7虛擬列，值得收藏

我們都知道where條件如果在字段上帶了函數(shù)就不會去走索引，不好優(yōu)化，無意間了解到mysql一個新特性--虛擬列，專門處理這塊問題的，下面一起來了解下吧~

在MySQL 5.7中，支持兩種Generated Column，即 Virtual Generated Column和Stored Generated Column ，前者只將Generated Column保存在數(shù)據(jù)字典中（表的元數(shù)據(jù)），并不會將這一列數(shù)據(jù)持久化到磁盤上；后者會將Generated Column持久化到磁盤上，而不是每次讀取的時候計算所得。很明顯，后者存放了可以通過已有數(shù)據(jù)計算而得的數(shù)據(jù)，需要更多的磁盤空間，與Virtual Column相比并沒有優(yōu)勢，因此，MySQL 5.7中，不指定Generated Column的類型，默認是Virtual Column。

如果需要Stored Generated Golumn的話，可能在Virtual Generated Column上建立索引更加合適。綜上，一般情況下，都使用Virtual Generated Column，這也是MySQL默認的方式

假設(shè)有一個表，其中包含一個 date 類型的列 `SimpleDate` date

SimpleDate 是一個常用的查詢字段，并需要對其執(zhí)行日期函數(shù)，例如

此時的問題是 即使對 SimpleDate 建立索引，這個查詢語句也無法使用，因為日期函數(shù)阻止了索引。

為了提高查詢效率，通常要進行額外的操作，例如新建一個字段 SimpleDate_dayofweek，存放 dayofweek(SimpleDate) 的計算結(jié)果，然后對這列創(chuàng)建索引，SimpleDate_dayofweek 的值需要程序?qū)懭?，例如使用觸發(fā)器，在 SimpleDate 有變動時更新這樣查詢就可以改為

這么做的好處是提高了查詢性能，可以使用 SimpleDate_dayofweek 列的索引了，但又帶來了其他麻煩，例如

虛擬列 Generated Columns 就是用來解決這個問題的，可以增加一個可被索引的列，但實際上并不存在于數(shù)據(jù)表中，下面用一個實驗來說明下：

需求：為了實現(xiàn)對json數(shù)據(jù)中部分數(shù)據(jù)的索引查詢，考慮用MySQL5.7中的虛擬列功能

1、創(chuàng)建表

2、準備數(shù)據(jù)

3、構(gòu)建姓名的虛擬列

4、構(gòu)建索引

5、測試是否用到索引

可以看出用了索引了

6、插入新數(shù)據(jù)

此時的表的結(jié)構(gòu)由于多出了user_name這一虛擬列，再插入別的數(shù)據(jù)要注意在表后指明插入列（不能給虛擬列插入數(shù)據(jù)）

做完發(fā)現(xiàn)這個實驗好像不是那么好理解...應(yīng)該對比一下加不加虛擬列有沒走索引，可能會更容易讓大家理解的...后面會分享更多devops和DBA方面的內(nèi)容，感興趣的朋友可以關(guān)注一下~

本文題目：mysql虛擬列怎么用 mysql虛擬表的作用
標題網(wǎng)址：http://muchs.cn/article16/ddipsgg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供建站公司、軟件開發(fā)、商城網(wǎng)站、動態(tài)網(wǎng)站、服務(wù)器托管、Google

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)