原文地址
http://mp.weixin.qq.com/s?__biz=MjM5MjIxNDA4NA==&mid=400998070&idx=1&sn=a3fd251ddd047e089e80cae5d4d90c34&scene=0#wechat_redirect

正文

MySQL 5.7引入了Generated Column，這篇文章簡單地介紹了Generated Column的使用方法和注意事項，為讀者瞭解MySQL 5.7提供一個快速的、完整的教程。這篇文章圍繞以下幾個問題展開：
Generated Column是什麼
Virtual Column與Stored Column的區別
如果我對Generated Column做一些破壞行為會怎麼樣
Generated Column上建立索引
Generated Column上建立索引與Oracle的函式索引的區別
Generated Column是什麼
Generated Column是MySQL 5.7引入的新特性，所謂Cenerated Column，就是資料庫中這一列由其他列計算而得，我們以官方參考手冊中的例子予以說明。
例如，知道直角三角形的兩條直角邊，要求斜邊的長度。很明顯，斜邊的長度可以透過兩條直角邊計算而得，那麼，這時候就可以在資料庫中只存放直角邊，斜邊使用Generated Column，如下所示：
CREATE TABLE triangle (
sidea DOUBLE,
sideb DOUBLE,
sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)));
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
查詢結果：
mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec |
+-------+-------+--------------------+
| 1 | 1 | 1.4142135623730951 |
| 3 | 4 | 5 |
| 6 | 8 | 10 |
+-------+-------+--------------------+
這個例子就足以說明Generated Columns是什麼，以及怎麼使用用了。
Virtual Generated Column與Stored Generated Column的區別
在MySQL 5.7中，支援兩種Generated Column，即Virtual Generated Column和Stored Generated Column，前者只將Generated Column儲存在資料字典中（表的後設資料），並不會將這一列資料持久化到磁碟上；後者會將Generated Column持久化到磁碟上，而不是每次讀取的時候計算所得。很明顯，後者存放了可以透過已有資料計算而得的資料，需要更多的磁碟空間，與Virtual Column相比並沒有優勢，因此，MySQL 5.7中，不指定Generated Column的型別，預設是Virtual Column。此外：
Stored Generated Column效能較差，見這裡
如果需要Stored Generated Golumn的話，可能在Generated Column上建立索引更加合適，見本文第4部分的介紹
綜上，一般情況下，都使用Virtual Generated Column，這也是MySQL預設的方式，如果使用Stored Generated Column，前面的建表語句將會是下面這樣，即多了一個stored關鍵字：
Create Table: CREATE TABLE `triangle` (
`sidea` double DEFAULT NULL,
`sideb` double DEFAULT NULL,
`sidec` double GENERATED ALWAYS AS (SQRT(sidea * sidea + sideb * sideb)) STORED)

如果對generated column做一些破壞行為會怎麼樣?
我們已經知道了generated column是什麼，並且知道了如何使用generated column，為了避免誤用，我們先來進行一些實驗，以免在具體使用時出現一些未知的情況。
將generated column定義為 "除以0"
如果我們將generated column定義為 "x列 / 0"，MySQL並不會直接報錯，而是在插入資料時報錯，並提示"ERROR 1365 (22012): Division by 0"
mysql> create table t( x int, y int, z int generated always 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
插入惡意資料
如果我們將generated column定義為 "x列/y列"，在插入資料，如果y列為0的話，同樣提示錯誤，如下所示：
mysql> create table t( x int, y int, z int generated always as( x / y));
Query OK, 0 rows affected (0.20 sec)
mysql> insert into t(x,y) values(1,0);
ERROR 1365 (22012): Division by 0

刪除源列
如果我們將generated column定義為 "x列/y列"，並嘗試刪除x列或y列，將提示"ERROR 3108 (HY000): Column 'x' has a generated column dependency."
mysql> create table t( x int, y int, z int generated always as( x / y));
Query OK, 0 rows affected (0.24 sec)
mysql> alter table t drop column x;
ERROR 3108 (HY000): Column 'x' has a generated column dependency.

定義顯然不合法的Generated Column
如果我們將generated column定義為 "x列+y列"，很明顯，x列或y列都是數值型，如果我們將x列或y列定義(或修改)為字元型（當然，實際使用時應該不會有人傻到這樣去做），則預期會報錯，然而並沒有，如下所示，我們可以正常建立。
mysql> create table t( x int, y varchar(100), z int generated always as( x + y));
Query OK, 0 rows affected (0.13 sec)
並且插入如下這樣的資料也不會出錯：
mysql> insert into t(x,y) values(1,'0');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t;
+------+------+------+
| x | y | z |
+------+------+------+
| 1 | 0 | 1 |
+------+------+------+
1 row in set (0.00 sec)
但是對於MySQL無法處理的情況，則會報錯：
mysql> insert into t(x,y) values(1,'x');
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'x'
Generated Column上建立索引
同樣，我們可以在generated column上建立索引，建立索引以後，能夠加快查詢速度，如下所示：
mysql> create table t(x int primary key, y int, z int generated always as (x / y), unique key idz(z));
Query OK, 0 rows affected (0.11 sec)
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`x` int(11) NOT NULL,
`y` int(11) DEFAULT NULL,
`z` int(11) GENERATED ALWAYS AS (x / y) VIRTUAL,
PRIMARY KEY (`x`),
UNIQUE KEY `idz` (`z`)) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
並且，我們可以建立普通索引和唯一索引，如果是唯一索引，在違反了唯一性約束時，進行報錯：
mysql> insert into t(x,y) values(1,1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into t(x,y) values(2,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'idz'
所以，在使用MySQL5.7時，還需要對Generated Column有所瞭解，才能夠解決一些以前沒有遇到過的問題。

索引的限制

雖然一般情況下都應該使用Virtal Generated Column，但是，目前使用Virtual Generated Column還有很多限制，包括：
聚集索引不能包含virtual generated column
mysql> create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b), primary key(c));
ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated 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)
不能在Virtual Generated Column上建立全文索引和空間索引，這個在之後的MySQL版本中有望解決（Inside君咋記得Stored Column上市可以的呢？）。
Virtual Generated Column不能作為外來鍵
建立generated column(包括virtual generated column 和stored generated column)時不能使用非確定性的（不可重複的）函式
mysql> ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime()) virtual;
ERROR 3102 (HY000): Expression of generated column 'p3' contains a disallowed function.
mysql> ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime()) stored;
ERROR 3102 (HY000): Expression of generated column 'p3' contains a disallowed function.

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的函式索引，似乎代價會高很多。但是，我們在第2部分說過，對於Virtual Generated Column，MySQL只是將這一列的元資訊儲存在資料字典中，並不會將這一列資料持久化到磁碟上，因此，在MySQL的Virtual Generated Column上建立索引和Oracle的函式索引類似，並不需要更多的代價，只是使用方式有點不一樣而已。
參考資料
http://mysqlserverteam.com/json-labs-release-effective-functional-indexes-in-innodb/
http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns
http://mysqlserverteam.com/virtual-columns-and-effective-functional-indexes-in-innodb/

【Mysql】MySQL 5.7新特性之Generated Column（函式索引）

正文

索引的限制

Generated Column上建立索引與Oracle的函式索引的區別

相關文章