這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)碛嘘P(guān)怎么理解MySQL 5.7中的Generated Column，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

公司主營(yíng)業(yè)務(wù)：成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作、移動(dòng)網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭(zhēng)能力。創(chuàng)新互聯(lián)建站是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶帶來驚喜。創(chuàng)新互聯(lián)建站推出衛(wèi)東免費(fèi)做網(wǎng)站回饋大家。

 正文 

MySQL 5.7引入了Generated Column，這篇文章簡(jiǎn)單地介紹了Generated Column的使用方法和注意事項(xiàng)，為讀者了解MySQL 5.7提供一個(gè)快速的、完整的教程。這篇文章圍繞以下幾個(gè)問題展開： 

Generated Column是MySQL 5.7引入的新特性，所謂Cenerated Column，就是數(shù)據(jù)庫中這一列由其他列計(jì)算而得，我們以官方參考手冊(cè)中的例子予以說明。 

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

1. CREATE TABLE triangle ( 

2. sidea DOUBLE, 

3. sideb DOUBLE, 

4. sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))); 

5. INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8); 

   查詢結(jié)果： 

6. mysql> SELECT * FROM triangle; 

7. +-------+-------+--------------------+ 

8. | sidea | sideb | sidec | 

9. +-------+-------+--------------------+ 

10. | 1 | 1 | 1.4142135623730951 | 

11. | 3 | 4 | 5 | 

12. | 6 | 8 | 10 | 

13. +-------+-------+--------------------+ 

    這個(gè)例子就足以說明Generated Columns是什么，以及怎么使用用了。 

Virtual Generated Column與Stored Generated Column的區(qū)別 

在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)勢(shì)，因此，MySQL 5.7中，不指定Generated Column的類型，默認(rèn)是Virtual Column。此外： 

Stored Generated Column性能較差，見這里 

如果需要Stored Generated Golumn的話，可能在Generated Column上建立索引更加合適，見本文第4部分的介紹 

綜上，一般情況下，都使用Virtual Generated Column，這也是MySQL默認(rèn)的方式，如果使用Stored Generated Column，前面的建表語句將會(huì)是下面這樣，即多了一個(gè)stored關(guān)鍵字： 

1. Create Table: CREATE TABLE `triangle` ( 

2.  `sidea` double DEFAULT NULL, 

3.  `sideb` double DEFAULT NULL, 

4.  `sidec` double GENERATED ALWAYS AS (SQRT(sidea * sidea + sideb * sideb)) STORED) 

如果對(duì)generated column做一些破壞行為會(huì)怎么樣? 

我們已經(jīng)知道了generated column是什么，并且知道了如何使用generated column，為了避免誤用，我們先來進(jìn)行一些實(shí)驗(yàn)，以免在具體使用時(shí)出現(xiàn)一些未知的情況。 

1. 將generated column定義為 "除以0" 

2. 如果我們將generated column定義為 "x列 / 0"，MySQL并不會(huì)直接報(bào)錯(cuò)，而是在插入數(shù)據(jù)時(shí)報(bào)錯(cuò)，并提示"ERROR 1365 (22012): Division by 0" 

3. mysql> create table t( x int, y int, z int generated always as( x / 0)); 

4. Query OK, 0 rows affected (0.22 sec) 

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

6. ERROR 1365 (22012): Division by 0 

插入惡意數(shù)據(jù) 

如果我們將generated column定義為 "x列/y列"，在插入數(shù)據(jù)，如果y列為0的話，同樣提示錯(cuò)誤，如下所示： 

1. mysql> create table t( x int, y int, z int generated always as( x / y)); 

2. Query OK, 0 rows affected (0.20 sec) 

3. mysql> insert into t(x,y) values(1,0); 

4. ERROR 1365 (22012): Division by 0 

刪除源列 

 如果我們將generated column定義為 "x列/y列"，并嘗試刪除x列或y列，將提示"ERROR 3108 (HY000): Column 'x' has a generated column dependency." 

1. mysql> create table t( x int, y int, z int generated always as( x / y)); 

2. Query OK, 0 rows affected (0.24 sec) 

3. mysql> alter table t drop column x; 

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

定義顯然不合法的Generated Column 

 如果我們將generated column定義為 "x列+y列"，很明顯，x列或y列都是數(shù)值型，如果我們將x列或y列定義(或修改)為字符型（當(dāng)然，實(shí)際使用時(shí)應(yīng)該不會(huì)有人傻到這樣去做），則預(yù)期會(huì)報(bào)錯(cuò)，然而并沒有，如下所示，我們可以正常創(chuàng)建。 

1.  mysql> create table t( x int, y varchar(100), z int generated always as( x + y)); 

2.  Query OK, 0 rows affected (0.13 sec) 

3. 并且插入如下這樣的數(shù)據(jù)也不會(huì)出錯(cuò)： 

4. mysql> insert into t(x,y) values(1,'0'); 

5. Query OK, 1 row affected (0.01 sec) 

6. mysql> select * from t; 

7. +------+------+------+ 

8. | x | y | z | 

9. +------+------+------+ 

10. | 1 | 0 | 1 | 

11. +------+------+------+ 

12. 1 row in set (0.00 sec) 

但是對(duì)于MySQL無法處理的情況，則會(huì)報(bào)錯(cuò)： 

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

2. ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'x' 

3. Generated Column上創(chuàng)建索引 

4. 同樣，我們可以在generated column上建立索引，建立索引以后，能夠加快查找速度，如下所示： 

5. mysql> create table t(x int primary key, y int, z int generated always as (x / y), unique key idz(z)); 

6. Query OK, 0 rows affected (0.11 sec) 

7. mysql> show create table t\G 

8. *************************** 1. row *************************** 

9. Table: t 

10. Create Table: CREATE TABLE `t` (

11.   `x` int(11) NOT NULL,

12.   `y` int(11) DEFAULT NULL,

13.   `z` int(11) GENERATED ALWAYS AS (x / y) VIRTUAL,

14.   PRIMARY KEY (`x`),

15.   UNIQUE KEY `idz` (`z`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 

16. 1 row in set (0.01 sec) 

并且，我們可以創(chuàng)建普通索引和唯一索引，如果是唯一索引，在違反了唯一性約束時(shí)，進(jìn)行報(bào)錯(cuò)： 

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

2. Query OK, 1 row affected (0.02 sec) 

3. mysql> insert into t(x,y) values(2,2); 

4. ERROR 1062 (23000): Duplicate entry '1' for key 'idz' 

所以，在使用MySQL5.7時(shí)，還需要對(duì)Generated Column有所了解，才能夠解決一些以前沒有遇到過的問題。 

1. 索引的限制 

1. 雖然一般情況下都應(yīng)該使用Virtal Generated Column，但是，目前使用Virtual Generated Column還有很多限制，包括： 

2. 聚集索引不能包含virtual generated column 

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

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

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

6. Query OK, 0 rows affected (0.11 sec) 

7. 不能在Virtual Generated Column上創(chuàng)建全文索引和空間索引，這個(gè)在之后的MySQL版本中有望解決（Inside君咋記得Stored Column上市可以的呢？）。 

8. Virtual Generated Column不能作為外鍵 

9. 創(chuàng)建generated column(包括virtual generated column 和stored generated column)時(shí)不能使用非確定性的（不可重復(fù)的）函數(shù) 

10. mysql> ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime()) virtual; 

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

13. mysql> ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime()) stored; 

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

1. Generated Column上創(chuàng)建索引與Oracle的函數(shù)索引的區(qū)別 

1. 介紹完MySQL在Generated Column上的索引，熟悉Oracle的同學(xué)這時(shí)候可能會(huì)想起Oracle的函數(shù)索引，在MySQL的Generated Column列上建立索引與Oracle的函數(shù)索引比較類似，又有所區(qū)別： 

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

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

6. Query OK, 0 rows affected (0.11 sec) 

8. 假設(shè)這時(shí)候需要建一個(gè)full_name的索引，在Oracle中，我們可以直接在創(chuàng)建索引的時(shí)候使用函數(shù)，如下所示： 

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

11. 但是，上面這條語句在MySQL中就會(huì)報(bào)錯(cuò)。在MySQL中，我們可以先新建一個(gè)Generated Column，然后再在這個(gè)Generated Column上建索引，如下所示： 

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

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

乍一看，MySQL需要在表上增加一列，才能夠?qū)崿F(xiàn)類似Oracle的函數(shù)索引，似乎代價(jià)會(huì)高很多。但是，我們?cè)诘?部分說過，對(duì)于Virtual Generated Column，MySQL只是將這一列的元信息保存在數(shù)據(jù)字典中，并不會(huì)將這一列數(shù)據(jù)持久化到磁盤上，因此，在MySQL的Virtual Generated Column上建立索引和Oracle的函數(shù)索引類似，并不需要更多的代價(jià)，只是使用方式有點(diǎn)不一樣而已。 

上述就是小編為大家分享的怎么理解MySQL 5.7中的Generated Column了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。