参考官网:https://dev.mysql.com/doc/refman/5.7/en/replication-features-triggers.html
需要了解复制和触发器关系的背景:
程序变更的数据和账单对不上,但从程序上无法找到问题,于是开发人员想在数据库层记录通过触发器来记录每次变更的历史数据和新数据
创建触发器的语句
# 原始表t1
# 创建存储变更信息的表_trigger_t1,将去除AUTO_INCREMENT和自增键,因为每条信息都会变更很多次对应的同一主键值记录会插入多次;每个表只能有一个自增列且自增列是唯一键
CREATE TABLE `_trigger_t1` (
`id` int(20) unsigned NOT NULL ,
`col` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_col` (`col`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
# 记录最原始的记录
insert into _trigger_t1 select * from t1;
# 对新表添加自增列
alter table t1 ADD _record_id INT AUTO_INCREMENT PRIMARY KEY
FIRST;
#添加标识列
ALTER TABLE _trigger_t1 ADD _changeSign VARCHAR(20); #1 增加 2 修改 3删除
#添加变更时间
ALTER TABLE _trigger_t1 ADD _add_time DATETIME;
# 创建存储器
DELIMITER $$
CREATE TRIGGER tr_t1_delete AFTER DELETE
ON t1
FOR EACH ROW BEGIN
UPDATE _trigger_t1
SET _changeSign='delete',_add_time=CURRENT_TIMESTAMP()
WHERE id=old.id;
END$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER tr_t1_update AFTER UPDATE
ON t1
FOR EACH ROW BEGIN
INSERT INTO _trigger_t1
(
`id`,
`col`,
`_changeSign`,
`_add_time`
)
VALUES
(
NEW.`id`,
NEW.`col`,
'update' ,CURRENT_TIMESTAMP()
);
END$$
DELIMITER ;
CREATE TRIGGER tr_t1_insert AFTER INSERT
ON t1
FOR EACH ROW BEGIN
INSERT _trigger_t1
(
`id`,
`col`,
`_changeSign`,
`_add_time`
)
VALUES
(
NEW.`id`,
NEW.`col`,
'insert' ,CURRENT_TIMESTAMP()
);
END$$
DELIMITER ;
问题总结:
1、复制如果基于row模式,则在trigger在slave上失效,因为如果不这样设计,同一变更操作在master上记录一次等到复制到从库时在从库上又触发一次就会记录两次。
2、此次涉及的业务表较大,都是百万级别,共涉及到4张表,每个表上3个触发器,而且其中有的表上有外键约束,就导致每次变更原表时,速度慢了很多,后来就只能再删除触发器
3、一开始执行时建表错误,但trigger创建成果,这时候如果原表上变更记录就会调用触发器失败,以为触发器中的语句块中使用到的表不存在,从而导致变更原表失败
这个影响很大,所以在变更前一定要先预估影响范围