事件+存储过程+触发器

SET FOREIGN_KEY_CHECKS=0;   //无外键约束
SHOW VARIABLES LIKE 'event_scheduler';   //查看事件是否开启
SET GLOBAL event_scheduler = ON;     //设置事件为开启状态（临时）

注意：如果要永久生效，则要在my.cnf中添加：event_scheduler=ON。

-- ----------------------------
-- Table structure for test_table1
-- ----------------------------
DROP TABLE IF EXISTS `test_table1`;
CREATE TABLE `test_table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增长主键',
  `keyStr` varchar(32) NOT NULL COMMENT '当前登录用户uuid',
  `valueStr` varchar(32) DEFAULT NULL COMMENT '事件UUID',
  `createTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `keyStr` (`keyStr`)
) ENGINE=InnoDB AUTO_INCREMENT=562 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `test_table2`;
CREATE TABLE `test_table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增长主键',
  `keyStr` varchar(32) NOT NULL COMMENT '当前登录用户uuid',
  `valueStr` varchar(32) DEFAULT NULL COMMENT '事件UUID',
  `createTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `keyStr` (`keyStr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- 创建测试存储过程，每次往table1中插入2条数据
-- ----------------------------
DROP PROCEDURE IF EXISTS `pro_test1`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test1`()
BEGIN
  DECLARE i INT DEFAULT 0 ;
	SET i = 0;
  WHILE (i < 2) DO
    INSERT INTO test_table1(keyStr,valueStr,createTime) VALUES('key', 'value', NOW()) ;
  SET i = i + 1 ;
  END WHILE;
END
;;
DELIMITER ;


-- ----------------------------
-- 创建测试事件，从当前时间+间隔3分钟的时间开始，每分钟执行一次，调用存储过程
-- ----------------------------
DROP EVENT IF EXISTS `event_test`;
DELIMITER ;;
CREATE EVENT `event_test` ON SCHEDULE EVERY 1 MINUTE STARTS NOW()+ INTERVAL 3 MINUTE ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
  CALL pro_test1();
END
;;
DELIMITER ;


-- 当对test_table1表有插入操作时，就触发，并将table1中的数据插入到table2中，new代表新插入的对象
DROP TRIGGER IF EXISTS `trigger_insert_test_table2`;
CREATE TRIGGER trigger_insert_test_table2
AFTER INSERT ON test_table1
FOR each ROW
BEGIN
  REPLACE INTO test_table2(keyStr,valueStr,createTime) VALUES(new.keyStr, new.valueStr, new.createTime);
END;




-- 当对test_table1表有删除操作时，就触发，并将table2中createTime相同的删除掉，old代表刚删除的对象
DROP TRIGGER IF EXISTS `trigger_delete_test_table2`;
CREATE TRIGGER trigger_delete_test_table2
AFTER DELETE ON test_table1
FOR each ROW
BEGIN
  DELETE from test_table2 where createTime=old.createTime;
END;