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

最近看到Percona的工程師Agustín寫了一篇部落格,是關于MySQL觸發器和可更新視圖的一個觀點，具體連結可以參考

https://www.percona.com/blog/2017/06/14/triggers-and-updatable-views/

官方文檔對于觸發器的基本描述是這樣的：

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觸發器僅由SQL語句對表級觸發，視圖不可以，API級别的表級操作也不會觸發。

這個描述看起來沒什麼問題，畢竟觸發器是确實存在于具體的表上的，由表來觸發聽起來無可厚非。但是Agustín認為官方文檔的描述不夠嚴謹，而且主動送出了一個bug給官方，當然他這麼說，一來是對這方面的内容有深入的了解，而另外一方面是他做了大量的測試，涵蓋了MySQL

5.5, 5.6, 5.7.18（目前最新的版本），是以就事論事，這是一種很專業，嚴謹的态度。

Agustín測試的步驟如下：

他建立了一個測試表main_table,一個資訊記錄表 table_trigger_control，一個視圖view_main_table.

當然我也按捺不住，自己也測試一把,當然我是在在他的基礎上做了調整，适當簡化了下測試過程。

我們建立一個兩個表，一個是基表，一個是記錄表，一個是視圖。

基表

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;

然後建立3個觸發器，分别對應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');  

測試的場景相對比較簡單，就是測試DML的幾個場景即可，比如：

1）insert 3行資料

2）update 第2行

3）delete 第3行

具體的語句如下：

 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;測試之後，我們來看看最後的結果：

 select *from main_table;

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

| id | letters | numbers | time     |

|  1 | A       |      10 | 23:03:09 |

|  2 | MOD     |      20 | 23:03:13 |

+----+---------+---------+----------+ 而觸發器觸發後的資訊記錄在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個Insert,1個update,1個delete，剛好是5個。

這裡看起來沒有什麼特别的，我們來看看視圖的情況，也是這裡測試的一個關鍵。

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;

語句運作後的結果如下：

[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)而觸發器觸發後的資訊記錄表内容如下：

|    4 | AFTER INSERT |

|    5 | AFTER INSERT |

|    6 | AFTER INSERT |

|    5 | AFTER UPDATE |

|    6 | AFTER DELETE |

10 rows in set (0.00 sec)由此看來，也是成功觸發了5次。

這麼看來和表的效果一樣啊。

我們換一個姿勢，建立一個新的視圖：

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

然後繼續插入一條記錄，結果就報錯了。

> 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這個時候不确定before insert的觸發器觸發了嗎，可以再補充一個觸發器。

CREATE TRIGGER trigger_before_insert BEFORE INSERT ON main_table FOR EACH ROW

    INSERT INTO table_trigger_control VALUES (NEW.id, "BEFORE INSERT");繼續嘗試，還是失敗。   

ERROR 1471 (HY000): The target table view_main_table_temp of the INSERT is not insertable-into檢視觸發器控制資訊表，會發現沒有任何新增的記錄，可見這種類型的視圖是不會成功觸發的。

> select *from table_trigger_control;

10 rows in set (0.00 sec)當然不光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/

 當然官方的态度也是值得認可的，很快就确認了這個bug,将會馬上更新。

是以說，為社群共享也有很多種方式，對技術保持好奇心是學習進步的永恒動力。