mysql如何临时禁用触发器

最新推荐文章于 2024-04-30 15:34:02 发布

weixin_34226706

最新推荐文章于 2024-04-30 15:34:02 发布

阅读量2.2k

点赞数 3

文章标签：数据库 java python

原文链接：https://my.oschina.net/u/1010578/blog/379416

版权

2019独角兽企业重金招聘Python工程师标准>>>

mysql如何临时禁用触发器

###起因 Mysql的触发器，在触发控制上，只能按照对数据的操作方式（Insert，Update，Delete）以及操作前后（before，after）进行触发控制。但是如果碰到以下需求又该如何：对于A表的Insert语句，只有符合某些条件的数据触发Insert触发器。

###自己当初条件反射的写法在对应的触发器语句中，增加条件判断的逻辑。举个栗子：有个用户信息表user，有个通讯录表addressbook，两张表表结构类似，业务需求上某些数据需要做数据实时同步，即user有更新，addressbook也要更新。用户表有个Insert触发器，只把性别为男的用户数据插入到addressbook中。两表结构如下：

#为方便起见两个表结构就一模一样了，真实环境下一般都是部分字段类似而已
#性别字段取值1男0女 
CREATE TABLE `user` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL,
	`sex` TINYINT(4) NULL DEFAULT '0',
	`age` INT(11) NULL DEFAULT '0',
	`phone` VARCHAR(50) NULL DEFAULT NULL,
	`qq` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
;

CREATE TABLE `addressbook` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL,
	`sex` TINYINT(4) NULL DEFAULT '0',
	`age` INT(11) NULL DEFAULT '0',
	`phone` VARCHAR(50) NULL DEFAULT NULL,
	`qq` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
;

此时user表的Insert触发器写法：

CREATE DEFINER=`root`@`localhost` TRIGGER `user_insert_trigger` BEFORE INSERT ON `user` FOR EACH ROW 

BEGIN
	if new.sex = 1 
	then 
		insert into addressbook (name,sex,age,qq,phone) 
			values (new.name,new.sex,new.age,new.qq,new.phone)
		;
	end if;
	
END

经常码代码的程序猿们，应该最先想到的是这种写法吧，毕竟符合咱们平常写代码的逻辑。但是需求往往不是那么简单的，这头怪兽总是会向着我们不可预期的方向发展。再举个栗子：现在只要同步这些用户：年龄大于30岁，qq号小于8位的，使用189手机的男用户。（卧槽(╯‵□′)╯︵┻━┻）。现在的触发器语句可能是这样子：

CREATE DEFINER=`root`@`localhost` TRIGGER `user_insert_trigger` BEFORE INSERT ON `user` FOR EACH ROW BEGIN
	if new.sex = 1 and  substring(new.phone,1,3)=189 and length(new.qq) <=8 and new.age >30
	then 
		insert into addressbook (name,sex,age,qq,phone) 
		values (new.name,new.sex,new.age,new.qq,new.phone)
			 ;
	end if;
END

不就是if多了几个and条件，多使用了几个sql函数而已嘛！这的是这样吗？如果手机的判断条件变成是“福建电信用户”（不止是189号头哦）呢？还有更奇葩的需求：如果要实现user表和addressbook表双向同步怎么办，没错，就是user表跟addressbook表都有insert触发器往对方表插数据，这在mysql下是不允许的（防止循环触发）。当时碰到这个需求我竟然脑袋一热也用上面的方法这么写，触发器插入前判断new的数据在要插入的表里存不存在orz。总结一下，上面的触发器写法有很多缺点：

当对触发的数据有过滤需求时，需要用数据库语言写很多条件判断语句，有些在程序语言中很好实现的，在数据库语言中非常困难。
不利于理解与维护。由于有了1中那么多逻辑判断代码，导致触发器的语句会很冗长且复杂，阅读起来非常困难，即使加了注释。
不利于调试。数据库语言毕竟不像其他程序设计语言，你想打断点或者输出日志看是什么原因没有触发吗？图样，只能将insert数据插入到一张临时表里查看。
如果两张表有互相insert触发往对方表增加新数据的逻辑，这种写法要么不可行，要么作死。

###更灵活优雅的写法方法是使用mysql的session级变量控制触发器触发，然后把上面的一堆判断逻辑放到程序代码中。这是在google上找到的stackoverflow帖子，感觉挺有用的。举个栗子，上面的触发器可以这么写：

CREATE DEFINER=`root`@`localhost` TRIGGER `user_insert_trigger` BEFORE INSERT ON `user` FOR EACH ROW 

#这里@disable_triggers 是自定义的session变量（mysql中约定session变量用@开头，只对某一次会话有效，不影响其他会话），保证使用时各个触发器名字不一样就好
BEGIN
	IF @disable_triggers IS NULL THEN
		insert into addressbook (name,sex,age,qq,phone) 
		values (new.name,new.sex,new.age,new.qq,new.phone)
		;
	END IF;
	
END

如果在使用insert语句时，不想触发触发器，在sql语句前后加上这么两句即可。

#变量设为非NULL，这样不会进入触发器的相关操作
SET @disable_triggers = 1;

#不打算触发触发器的insert语句
insert into user values(....);

#上面语句执行完毕完毕后，重新将变量设置为NULL，重新开启触发逻辑
SET @disable_triggers = NULL;

对于某些更新数据时暂时想禁用触发器的情况，这种方法就可以先在程序代码中实现判断，然后决定要不要在执行的sql语句前后加入SET @disable_triggers = 1; 和 SET @disable_triggers = NULL; 来临时禁止触发器触发操作，触发器的代码就可以做到非常简洁而且容易维护。将判断逻辑放到程序代码中也方便debug。

不过在实践中发现此方法要注意以下几点(我使用的开发语言为java)：

SET @disable_triggers = 1; SET @disable_triggers = NULL; 这两个语句必须与要执行的sql放在同一条sql中，然后一起提交执行。不能先执行 SET @disable_triggers = 1; 再执行数据更新语句，再执行SET @disable_triggers = NULL; ，因为使用的变量是session变量，只有在同一个会话中，触发器才能找到disable_triggers变量。如果分成三次提交执行，相当于三个会话，触发器那边得到的disable_triggers变量还是为NULL。
对于java中的 sql prepared statement 预处理执行sql语句的方式，以上设置session变量的方法会抛异常。（预处理模式不支持一次同时提交多个sql语句？）我在项目中使用的是spring 的jdbctemplate，原生jdbc应该也是一样的。所以使用这种方法临时禁用触发器时，还是老实做好参数防注入判断，然后拼接sql语句执行吧，还好这种需求的场景不会很多。

转载于:https://my.oschina.net/u/1010578/blog/379416