MySQL中一個(gè)文檔疏漏的分析測(cè)試(r13筆記第3天)

最近看到Percona的工程師Agustín寫了一篇博客,是關(guān)于MySQL觸發(fā)器和可更新視圖的一個(gè)觀點(diǎn)，具體鏈接可以參考 https://www.percona.com/blog/2017/06/14/triggers-and-updatable-views/

官方文檔對(duì)于觸發(fā)器的基本描述是這樣的：

Important: MySQL triggers activate only for changes made to tables by SQL statements. They do not activate for changes in views, nor by changes to tables made by APIs that do not transmit SQL statements to the MySQL server.

 大體的翻譯就是：MySQL觸發(fā)器僅由SQL語(yǔ)句對(duì)表級(jí)觸發(fā)，視圖不可以，API級(jí)別的表級(jí)操作也不會(huì)觸發(fā)。

這個(gè)描述看起來(lái)沒(méi)什么問(wèn)題，畢竟觸發(fā)器是確實(shí)存在于具體的表上的，由表來(lái)觸發(fā)聽(tīng)起來(lái)無(wú)可厚非。但是Agustín認(rèn)為官方文檔的描述不夠嚴(yán)謹(jǐn)，而且主動(dòng)提交了一個(gè)bug給官方，當(dāng)然他這么說(shuō)，一來(lái)是對(duì)這方面的內(nèi)容有深入的理解，而另外一方面是他做了大量的測(cè)試，涵蓋了MySQL 5.5, 5.6, 5.7.18（目前最新的版本），所以就事論事，這是一種很專業(yè)，嚴(yán)謹(jǐn)?shù)膽B(tài)度。

Agustín測(cè)試的步驟如下：

他創(chuàng)建了一個(gè)測(cè)試表main_table,一個(gè)信息記錄表 table_trigger_control，一個(gè)視圖view_main_table.

當(dāng)然我也按捺不住，自己也測(cè)試一把,當(dāng)然我是在在他的基礎(chǔ)上做了調(diào)整，適當(dāng)簡(jiǎn)化了下測(cè)試過(guò)程。

我們創(chuàng)建一個(gè)兩個(gè)表，一個(gè)是基表，一個(gè)是記錄表，一個(gè)是視圖。

基表

CREATE TABLE `main_table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `letters` varchar(64) DEFAULT NULL,
    `numbers` int(11) NOT NULL,
    `time` time NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB ;

控制表
CREATE TABLE `table_trigger_control` (
      `id` int(11),
      `description` varchar(255)
    ) ENGINE=InnoDB  ;視圖
 CREATE VIEW view_main_table AS SELECT * FROM main_table;
然后創(chuàng)建3個(gè)觸發(fā)器，分別對(duì)應(yīng)insert，update，delete操作

CREATE TRIGGER trigger_after_insert after INSERT ON main_table FOR EACH ROW
    INSERT INTO table_trigger_control VALUES (NEW.id, "AFTER INSERT");
create trigger trigger_after_update after update on main_table for each row
     insert into table_trigger_control values(new.id,'AFTER UPDATE');    
     
create trigger trigger_after_delete after delete on main_table for each row
     insert into table_trigger_control values(old.id,'AFTER DELETE');  
測(cè)試的場(chǎng)景相對(duì)比較簡(jiǎn)單，就是測(cè)試DML的幾個(gè)場(chǎng)景即可，比如：
1）insert 3行數(shù)據(jù)
2）update 第2行
3）delete 第3行

具體的語(yǔ)句如下：

 INSERT INTO main_table VALUES (1, 'A', 10, time(NOW()));
 INSERT INTO main_table VALUES (2, 'B', 20, time(NOW()));
 INSERT INTO main_table VALUES (3, 'C', 30, time(NOW()));
 UPDATE main_table SET letters = 'MOD' WHERE id = 2;
 DELETE FROM main_table WHERE id = 3;測(cè)試之后，我們來(lái)看看最后的結(jié)果：

 select *from main_table;
+----+---------+---------+----------+
| id | letters | numbers | time     |
+----+---------+---------+----------+
|  1 | A       |      10 | 23:03:09 |
|  2 | MOD     |      20 | 23:03:13 |
+----+---------+---------+----------+ 而觸發(fā)器觸發(fā)后的信息記錄在table_trigger_control里面。

> SELECT * FROM table_trigger_control;
+------+--------------+
| id   | description  |
+------+--------------+
|    1 | AFTER INSERT |
|    2 | AFTER INSERT |
|    3 | AFTER INSERT |
|    2 | AFTER UPDATE |
|    3 | AFTER DELETE |
+------+--------------+
5 rows in set (0.00 sec)3個(gè)Insert,1個(gè)update,1個(gè)delete，剛好是5個(gè)。
這里看起來(lái)沒(méi)有什么特別的，我們來(lái)看看視圖的情況，也是這里測(cè)試的一個(gè)關(guān)鍵。

具體的語(yǔ)句如下：

INSERT INTO view_main_table VALUES (4, 'VIEW_D', 40, time(NOW()));
INSERT INTO view_main_table VALUES (5, 'VIEW_E', 50, time(NOW()));
INSERT INTO view_main_table VALUES (6, 'VIEW_F', 60, time(NOW()));
UPDATE view_main_table SET letters = 'VIEW_MOD' WHERE id = 5;
DELETE FROM view_main_table WHERE id = 6;

語(yǔ)句運(yùn)行后的結(jié)果如下：

[test]>  select *from main_table;
+----+----------+---------+----------+
| id | letters  | numbers | time     |
+----+----------+---------+----------+
|  1 | A        |      10 | 23:03:09 |
|  2 | MOD      |      20 | 23:03:13 |
|  4 | VIEW_D   |      40 | 23:04:43 |
|  5 | VIEW_MOD |      50 | 23:04:46 |
+----+----------+---------+----------+
4 rows in set (0.00 sec)而觸發(fā)器觸發(fā)后的信息記錄表內(nèi)容如下：
> SELECT * FROM table_trigger_control;
+------+--------------+
| id   | description  |
+------+--------------+
|    1 | AFTER INSERT |
|    2 | AFTER INSERT |
|    3 | AFTER INSERT |
|    2 | AFTER UPDATE |
|    3 | AFTER DELETE |
|    4 | AFTER INSERT |
|    5 | AFTER INSERT |
|    6 | AFTER INSERT |
|    5 | AFTER UPDATE |
|    6 | AFTER DELETE |
+------+--------------+
10 rows in set (0.00 sec)由此看來(lái)，也是成功觸發(fā)了5次。

這么看來(lái)和表的效果一樣啊。

我們換一個(gè)姿勢(shì)，創(chuàng)建一個(gè)新的視圖：

> CREATE ALGORITHM=TEMPTABLE VIEW view_main_table_temp AS SELECT * FROM main_table;

然后繼續(xù)插入一條記錄，結(jié)果就報(bào)錯(cuò)了。

> INSERT INTO view_main_table_temp VALUES (7, 'VIEW_H', 70, time(NOW()));
ERROR 1471 (HY000): The target table view_main_table_temp of the INSERT is not insertable-into這個(gè)時(shí)候不確定before insert的觸發(fā)器觸發(fā)了嗎，可以再補(bǔ)充一個(gè)觸發(fā)器。

CREATE TRIGGER trigger_before_insert BEFORE INSERT ON main_table FOR EACH ROW
    INSERT INTO table_trigger_control VALUES (NEW.id, "BEFORE INSERT");繼續(xù)嘗試，還是失敗。   

> INSERT INTO view_main_table_temp VALUES (7, 'VIEW_H', 70, time(NOW()));
ERROR 1471 (HY000): The target table view_main_table_temp of the INSERT is not insertable-into查看觸發(fā)器控制信息表，會(huì)發(fā)現(xiàn)沒(méi)有任何新增的記錄，可見(jiàn)這種類型的視圖是不會(huì)成功觸發(fā)的。

> select *from table_trigger_control;
+------+--------------+
| id   | description  |
+------+--------------+
|    1 | AFTER INSERT |
|    2 | AFTER INSERT |
|    3 | AFTER INSERT |
|    2 | AFTER UPDATE |
|    3 | AFTER DELETE |
|    4 | AFTER INSERT |
|    5 | AFTER INSERT |
|    6 | AFTER INSERT |
|    5 | AFTER UPDATE |
|    6 | AFTER DELETE |
+------+--------------+
10 rows in set (0.00 sec)當(dāng)然不光insert,update和delete也是一樣的效果。

 UPDATE view_main_table_temp SET letters = 'VIEW_MOD' WHERE id = 5;
 DELETE FROM view_main_table_temp WHERE id = 5;這方面Agustín特別提出了，在這方面MariaDB的文檔表述就值得贊了。
 https://mariadb.com/kb/en/mariadb/trigger-limitations/
 當(dāng)然官方的態(tài)度也是值得認(rèn)可的，很快就確認(rèn)了這個(gè)bug,將會(huì)馬上更新。

所以說(shuō)，為社區(qū)共享也有很多種方式，對(duì)技術(shù)保持好奇心是學(xué)習(xí)進(jìn)步的永恒動(dòng)力。