mysql 触发器实现需求

来源：互联网发布：程序员的思维方式编辑：程序博客网时间：2024/05/17 08:28

drop trigger if exists tg_bb_insert;  delimiter $$  create trigger tg_bb_insert  before insert on ceshi  FOR EACH ROW  BEGIN      declare ishave int;      select 1 into ishave from ceshiyong where id = New.id AND backupTs > CURDATE() AND backupTs < DATE_ADD(CURDATE(), INTERVAL 1 DAY) AND hour(now()) - hour(backupTs) < 2 order by backupTs desc limit 1;     if ishave is null then          insert into ceshiyong(id, count) values(NEW.id, New.count);      elseif ishave = 1 then          update ceshiyong set ceshiyong.count=New.count where id = New.id AND backupTs > CURDATE() AND backupTs < DATE_ADD(CURDATE(), INTERVAL 1 DAY) AND hour(now()) - hour(backupTs) < 2 order by backupTs desc limit 1;     else          -- 理论上不会调到 --        insert into ceshiyong(id, count) values(NEW.id, New.count);      end if;  END$$  delimiter ;

折腾一个需求：表1在做replace数据的时候，相同ID的数据，这次replace距离上次超过2小时就执行一下备份，懒的不愿写代码的关系，用触发器实现，这尼玛也是一个悲催的过程，这条语句折腾了我4个多小时。。纪念

mysql> desc ceshi;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    | int(11) | YES  |     | NULL    |       || count | int(11) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+2 rows in set (0.01 sec)mysql> desc ceshiyong;+----------+-----------+------+-----+-------------------+-------+| Field    | Type      | Null | Key | Default           | Extra |+----------+-----------+------+-----+-------------------+-------+| id       | int(11)   | YES  |     | NULL              |       || count    | int(11)   | YES  |     | NULL              |       || backupTs | timestamp | NO   |     | CURRENT_TIMESTAMP |       |+----------+-----------+------+-----+-------------------+-------+3 rows in set (0.01 sec)

0 0