背景:
生产上某张Main设备表,每秒近1900的update,导致了主从的延迟,做一个触发器,统计出update异常偏高的设备型号,提供给平台做如类似黑名单中。(每次更新都会更新update_time字段)
create table device_update(
sub_serial varchar(64) NOT NULL COMMENT '设备短序列号',
nums int NOT NULL COMMENT '更新次数',
PRIMARY key (sub_serial)
)ENGINE=innodb DEFAULT CHARSET=utf8;
delimiter |
CREATE TRIGGER tr1
AFTER UPDATE ON device_info
FOR EACH ROW
BEGIN
SET @old_update_time = OLD.update_time;
SET @new_update_time = NEW.update_time;
SET @sub_serial = NEW.sub_serial;
IF @old_update_time <> @new_update_time THEN
INSERT INTO test.device_update select @sub_serial,1 from dual ON DUPLICATE KEY UPDATE nums=nums+1;
end if;
end;
|