對(duì)于想要將自動(dòng)生成的數(shù)據(jù)添加到表中的任何人來說， 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è)要多少錢，請找那個(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ù)從同一行的其他列中讀取的其他值來生成生成列的值。因此，生成的列在表的域內(nèi)工作，其定義不能涉及 JOIN 語句。
換句話說，您可以將生成的列視為一種視圖，但僅限于列。請注意，生成的列與 SQL 觸發(fā)器 不同，您只能在使用 CREATE TABLE or語句時(shí)定義它們，語法如下： ALTER TABLE
該 AS (generated_column_expression) 子句指定要添加或更新到表中的列是生成的列。定義 MySQL 將用于計(jì)算列值的 generation_expression 表達(dá)式，它不能引用另一個(gè)生成的列或除當(dāng)前表的列之外的任何內(nèi)容。另外，請注意生成表達(dá)式只能涉及不可變函數(shù)。例如，您不能在生成的列表達(dá)式定義中使用返回當(dāng)前日期的函數(shù)，因?yàn)樗且粋€(gè)可變函數(shù)。
您還可以在關(guān)鍵字前面 AS 加上 GENERATED ALWAYS 關(guān)鍵字以使生成的列的性質(zhì)更加明確，但這是可選的。然后，您可以指示生成列的類型是 VIRTUAL 還是 STORED 。您將在下面的章節(jié)中了解這兩種類型之間的區(qū)別。默認(rèn)情況下，如果沒有在查詢中明確指定，MySQL 會(huì)將生成的列標(biāo)記為 VIRTUAL .
現(xiàn)在讓我們看看生成的列語法在 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ā)生。換句話說，虛擬生成的列不占用存儲(chǔ)空間。
MySQL 存儲(chǔ)任何生成的標(biāo)記為 STORED 的列。這意味著每次插入或更新行時(shí)，MySQL 都會(huì)評(píng)估其值并將其存儲(chǔ)在磁盤上。換句話說，存儲(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è)是最重要的。
如您所見，您可以通過將四列與以下生成的列聚合來輕松生成此數(shù)據(jù)字段：
這將產(chǎn)生：
在這種情況下，生成的列使您能夠直接在數(shù)據(jù)庫級(jí)別標(biāo)準(zhǔn)化數(shù)據(jù)字段格式。此外，存儲(chǔ)生成的列避免了每次需要時(shí)都構(gòu)造此字段的不可避免的開銷。
通常，您使用網(wǎng)站 URL 中的資源 ID 或REST API來檢索您需要的數(shù)據(jù)。但是公開暴露您的 ID 可能會(huì)帶來安全問題。當(dāng)您發(fā)現(xiàn)自己使用自動(dòng)增量 ID 時(shí)尤其如此，這很容易預(yù)測并使抓取或機(jī)器人攻擊更容易。
為避免這種情況，您可以考慮通過使用自動(dòng)生成的、隨機(jī)的、更安全的公共 ID 來隱藏您的原始 ID。您可以通過對(duì)您的 ID 進(jìn)行散列處理，使用虛擬生成的列來實(shí)現(xiàn)這一點(diǎn)，如下所示：
請注意，為避免生成已知的哈希值，您可以將您的 ID 與特殊關(guān)鍵字連接起來。 在此處了解有關(guān) MySQL 加密和壓縮功能的更多信息。
過濾數(shù)據(jù)時(shí)，有些列比其他列更有用。此外，您通常必須更改存儲(chǔ)在列中的值的表示形式，以使過濾更簡單或更直觀。您可以定義一個(gè)有用的生成列來存儲(chǔ)以所需格式執(zhí)行過濾所需的信息，而不是在每個(gè)過濾器查詢中執(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 換句話說，觸發(fā)器可以涉及多個(gè)表和所有 MySQL 函數(shù)。與生成的列相比，這使它們成為更完整的解決方案。同時(shí)，MySQL 觸發(fā)器本質(zhì)上使用和定義更復(fù)雜，也比生成的列慢。
我們都知道where條件如果在字段上帶了函數(shù)就不會(huì)去走索引，不好優(yōu)化，無意間了解到mysql一個(gè)新特性--虛擬列，專門處理這塊問題的，下面一起來了解下吧~
在MySQL 5.7中，支持兩種Generated Column，即 Virtual Generated Column和Stored Generated Column ，前者只將Generated Column保存在數(shù)據(jù)字典中（表的元數(shù)據(jù)），并不會(huì)將這一列數(shù)據(jù)持久化到磁盤上；后者會(huì)將Generated Column持久化到磁盤上，而不是每次讀取的時(shí)候計(jì)算所得。很明顯，后者存放了可以通過已有數(shù)據(jù)計(jì)算而得的數(shù)據(jù)，需要更多的磁盤空間，與Virtual Column相比并沒有優(yōu)勢，因此，MySQL 5.7中，不指定Generated Column的類型，默認(rèn)是Virtual Column。
如果需要Stored Generated Golumn的話，可能在Virtual Generated Column上建立索引更加合適。綜上，一般情況下，都使用Virtual Generated Column，這也是MySQL默認(rèn)的方式
假設(shè)有一個(gè)表，其中包含一個(gè) date 類型的列 `SimpleDate` date
SimpleDate 是一個(gè)常用的查詢字段，并需要對(duì)其執(zhí)行日期函數(shù)，例如
此時(shí)的問題是 即使對(duì) SimpleDate 建立索引，這個(gè)查詢語句也無法使用，因?yàn)槿掌诤瘮?shù)阻止了索引。
為了提高查詢效率，通常要進(jìn)行額外的操作，例如新建一個(gè)字段 SimpleDate_dayofweek，存放 dayofweek(SimpleDate) 的計(jì)算結(jié)果，然后對(duì)這列創(chuàng)建索引，SimpleDate_dayofweek 的值需要程序?qū)懭?，例如使用觸發(fā)器，在 SimpleDate 有變動(dòng)時(shí)更新這樣查詢就可以改為
這么做的好處是提高了查詢性能，可以使用 SimpleDate_dayofweek 列的索引了，但又帶來了其他麻煩，例如
虛擬列 Generated Columns 就是用來解決這個(gè)問題的，可以增加一個(gè)可被索引的列，但實(shí)際上并不存在于數(shù)據(jù)表中，下面用一個(gè)實(shí)驗(yàn)來說明下：
需求：為了實(shí)現(xiàn)對(duì)json數(shù)據(jù)中部分?jǐn)?shù)據(jù)的索引查詢，考慮用MySQL5.7中的虛擬列功能
1、創(chuàng)建表
2、準(zhǔn)備數(shù)據(jù)
3、構(gòu)建姓名的虛擬列
4、構(gòu)建索引
5、測試是否用到索引
可以看出用了索引了
6、插入新數(shù)據(jù)
此時(shí)的表的結(jié)構(gòu)由于多出了user_name這一虛擬列，再插入別的數(shù)據(jù)要注意在表后指明插入列（不能給虛擬列插入數(shù)據(jù)）
做完發(fā)現(xiàn)這個(gè)實(shí)驗(yàn)好像不是那么好理解...應(yīng)該對(duì)比一下加不加虛擬列有沒走索引，可能會(huì)更容易讓大家理解的...后面會(huì)分享更多devops和DBA方面的內(nèi)容，感興趣的朋友可以關(guān)注一下~
因?yàn)橐恍﹤€(gè)人習(xí)慣的原因，我在設(shè)計(jì)數(shù)據(jù)表的時(shí)候比較偏向在表的最后一列添加一個(gè)類型為 json 的 extra 字段，專門存放一些非格式化字段，偶爾記一個(gè) label，tag等非常便捷。但是現(xiàn)在遇到一個(gè)問題，json 中的某個(gè)字段在某些業(yè)務(wù)的時(shí)候被大量查詢。
在 table 比較小的時(shí)候絲毫不影響效率，但是隨著 table 增大，應(yīng)該打到一個(gè)臨界的時(shí)候查詢速度驟降，一個(gè)簡單的查詢需要掃描 100w+ 行，查詢時(shí)間從 3s 到 80s 不等。
常規(guī)操作時(shí)添加一個(gè)索引，但是由于查詢的是 json 中的某個(gè) key，mysql 目前沒有辦法直接給 json 字段添加索引，但是有一個(gè)曲線救國的辦法，將其中的字段抽出來作為一個(gè)虛擬字段，然后給這個(gè)字段添加索引，這時(shí)候 mysql 就會(huì)緩存該字段，我們在需要查詢的地方去使用虛擬字段就可以了，因?yàn)樽叩氖撬饕樵兯俣忍貏e快。
當(dāng)然還有另外一個(gè)方法，就是將 json 的 key 提取出來作為一個(gè)新字段，這樣做也可以但是需要修改很多地方，特別是在有很多插入和更新的操作的時(shí)候，還有可能引入新的 bug。
從MySQL 5.7開始，支持兩種Generated Column，即Virtual Generated Column和Stored Generated Column;
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 ))
重慶分公司
重慶分公司
