MySQL中一个文档疏漏的分析测试

最近看到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,将会马上更新。

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

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-06-23

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Python

异常处理:1215 - Cannot add foreign key constraint

  最近在做新生入学系统,学生表中包括新生的班级,专业等信息,班级,专业就需要和班级表,专业表进行关联,但是在添加外键的过程中却出现了“Cannot add f...

238100
来自专栏游戏杂谈

Node.js调用mysql的存储过程

例子仅在windows下测试通过,没有放在linux下测试。如有问题,可以电邮给我~

20110
来自专栏Netkiller

数据库安全·Token 认证

以下节选择《Netkiller Architect 手札》 作者:netkiller 地址 http://www.netkiller.cn/archit...

39840
来自专栏算法channel

MySQL|索引应用

可以先看下这篇理论介绍: MySQL|索引背后 01 MySQL的几种KEY PRIMARY KEY 有两个作用,一是约束作用(constraint),用来...

35370
来自专栏java达人

oracle、mysql 分页查询比较

1、 Oracle的分页查询语句 分页查询格式: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * ...

40290
来自专栏王磊的博客

MS SQL查询库、表、列数据结构信息汇总

前言 一般情况我们下,我们是知道数据库的表、列信息的(因为数据库是我们手动设计),但特殊情况下,如果你只能拿到数据库连接信息,也就是知道的一个数据库名的情况下,...

44240
来自专栏java达人

oracle、mysql 分页查询比较

1、 Oracle的分页查询语句 分页查询格式: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * ...

31180
来自专栏西安-晁州

sqlserver - FOR XML PATH

FOR XML PATH 有的人可能知道有的人可能不知道,其实它就是将查询结果集以XML形式展现,有了它我们可以简化我们的查询语句实现一些以前可能需要借助函数活...

22900
来自专栏闵开慧

phpmyadmin中导入文件时显示 No database selected

错误 SQL 查询: -- 数据库: `7789_pay` -- -- -------------------------------------...

41660
来自专栏企鹅号快讯

python数据处理实战-自动统计mysql数据库数据表每天数据量

日常报表统计,日总量,日增量不可避免,这篇文章我们从实际应用出发,从逻辑思考到最后写出代码,一步步分析拆解 一.表结构设计 既然想统计每一张表每天的数据量,后续...

57870

扫码关注云+社区

领取腾讯云代金券