mysql 5.7 generated_MySQL 5.7新特性之Generated Column（函數索引）

官網原文：https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

注：以下是我參考官網文檔和結合自己的理解寫的，因為英文水平有限，不排除有錯誤，歡迎糾錯。

一、說明

Generated Column是MySQL 5.7引入的新特性，所謂Cenerated Column，就是數據庫中這一列由其他列計算而得。在MySQL 5.7中，支持兩種Generated Column，即Virtual Generated Column(虛擬生成的列)和Stored Generated Column(存儲生成的列)，二者含義如下：

1、Virtual Generated Column(虛擬生成的列)：不存儲該列值，即MySQL只是將這一列的元信息保存在數據字典中，並不會將這一列數據持久化到磁盤上，而是當讀取該行時，觸發觸發器對該列進行計算顯示。InnoDB支持Virtual Generated Column，具體參考“https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html”

2、Stored Generated Column(存儲生成的列)：存儲該列值，即該列值在插入或更新行時進行計算和存儲。所以相對於Virtual Column列需要更多的磁盤空間，與Virtual Column相比並沒有優勢。因此，MySQL 5.7中，不指定Generated Column的類型，默認是Virtual Column

在表中允許Virtual Column和Stored Column的混合使用

提高效率：由於mysql在普通索引上加函數會造成索引失效，造成查詢性能下降，Generated Column(函數索引)剛好可以解決這個問題，可以在Generated Column加上索引來提高效率

二、創建規則

1 col_name data_type [GENERATED ALWAYS] AS(expression)2 [VIRTUAL | STORED] [NOT NULL | NULL]

3 [UNIQUE [KEY]] [[PRIMARY] KEY]4 [COMMENT 'string']

三、使用

例如，知道直角三角形的兩條直角邊，要求斜邊的長度。很明顯，斜邊的長度可以通過兩條直角邊計算而得，那么，這時候就可以在數據庫中只存放直角邊，斜邊使用Generated Column，如下所示：

-- 創建表

CREATE TABLEtriangle (

sideaDOUBLE,

sidebDOUBLE,

sidecDOUBLE AS (SQRT(sidea * sidea + sideb *sideb))

);

-- 插入INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

-- 查詢

mysql> SELECT * FROMtriangle;+-------+-------+--------------------+

| sidea | sideb | sidec |

+-------+-------+--------------------+

| 1 | 1 | 1.4142135623730951 |

| 3 | 4 | 5 |

| 6 | 8 | 10 |

+-------+-------+--------------------+

通過這個例子就足以說明Generated Columns是什么，以及怎么使用了。

注意事項：

1、可以給出其他屬性來指示該列是否被索引或可以為null，或提供注釋。

2、我們在generated column上建立索引，建立索引以后，能夠加快查找速度

索引的限制：

雖然一般情況下都應該使用Virtal Generated Column，但是，目前使用Virtual Generated Column還有很多限制

1、聚集索引不能包含virtual generated column

mysql> create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b), primary key(c));

ERROR3106 (HY000): 'Defining a virtual generated column as primary key' is not supported forgenerated columns.

mysql> create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b) STORED, primary key(c));

Query OK,0 rows affected (0.11 sec)

2、不能在Virtual Generated Column上創建全文索引和空間索引

--全文索引

mysql> CREATE TABLE`article` ( `id`int(10) unsigned NOT NULLAUTO_INCREMENT, `title`varchar(200) DEFAULT NULL, `message`varchar(200) DEFAULT NULL, `content`text GENERATED ALWAYS AS(concat(`title`,`message`)),PRIMARY KEY(`id`), FULLTEXTKEY`content_idx1` (`content`) ) ;[Err] 3106 - 'Fulltext index on virtual generated column' is not supported forgenerated columns.--全文索引 mysql>CREATE TABLE`article` ( `id`int(10) unsigned NOT NULLAUTO_INCREMENT, `title`varchar(200) DEFAULT NULL, `message`varchar(200) DEFAULT NULL, `content`text GENERATED ALWAYS AS(concat(`title`,`message`)) STORED,PRIMARY KEY(`id`), FULLTEXTKEY`content_idx1` (`content`) ) ; Query OK,0 rows affected (0.11 sec)

3、Virtual Generated Column不能作為外鍵

4、創建generated column(包括virtual generated column 和stored generated column)時可以使用確定性內置函數和運算符,但不能使用非確定性的(不可重復的)函數。

mysql> ALTER TABLE `score` ADD deal_time DATE GENERATED ALWAYS AS(now()) virtual;[Err] 3102 - Expression of generated column 'deal_time' contains a disallowed function.

mysql> ALTER TABLE `score` ADD deal_time DATE GENERATED ALWAYS AS(now()) stored;[Err] 3102 - Expression of generated column 'deal_time' contains a disallowed function.

5、子查詢、參數、變量和用戶定義的函數都是不允許的

四、Generated Column上創建索引與Oracle的函數索引的區別

介紹完MySQL在Generated Column上的索引，熟悉Oracle的同學這時候可能會想起Oracle的函數索引，在MySQL的Generated Column列上建立索引與Oracle的函數索引比較類似，又有所區別：

例如有一張表，如下所示：

mysql> CREATE TABLE t1 (first_name VARCHAR(10), last_name VARCHAR(10));

Query OK,0 rows affected (0.11 sec)

假設這時候需要建一個full_name的索引，在Oracle中，我們可以直接在創建索引的時候使用函數，如下所示：

alter table t1 add index full_name_idx(CONCAT(first_name,' ',last_name));

但是，上面這條語句在MySQL中就會報錯。在MySQL中，我們可以先新建一個Generated Column，然后再在這個Generated Column上建索引，如下所示：

mysql> alter table t1 add column full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name));

mysql> alter table t1 add index full_name_idx(full_name);

乍一看，MySQL需要在表上增加一列，才能夠實現類似Oracle的函數索引，似乎代價會高很多。但是，我們在上面提過，對於Virtual Generated Column，MySQL只是將這一列的元信息保存在數據字典中，並不會將這一列數據持久化到磁盤上，因此，在MySQL的Virtual Generated Column上建立索引和Oracle的函數索引類似，並不需要更多的代價，只是使用方式有點不一樣而已。