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)而觸發器觸發後的資訊記錄表內容如下：
> 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)由此看來，也是成功觸發了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");繼續嘗試，還是失敗。   

> 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檢視觸發器控制資訊表，會發現沒有任何新增的記錄，可見這種型別的檢視是不會成功觸發的。

> 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)當然不光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的文件表述就值得讚了。
 
 當然官方的態度也是值得認可的，很快就確認了這個bug,將會馬上更新。

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