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的文档表述就值得赞了。
https://mariadb.com/kb/en/mariadb/trigger-limitations/
当然官方的态度也是值得认可的，很快就确认了这个bug,将会马上更新。

所以说，为社区共享也有很多种方式，对技术保持好奇心是学习进步的永恒动力。

相关文章：