mysql event建立模板（可记录执行履历）

1. 建立履历记录表
   

CREATE TABLE `mysql`.`t_event_history` (  `dbname` VARCHAR(128) NOT NULL DEFAULT '',  `eventname` VARCHAR(128) NOT NULL DEFAULT '',  `starttime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',  `endtime` DATETIME DEFAULT NULL,  `issuccess` INT(11) DEFAULT NULL,  `duration` INT(11) DEFAULT NULL,  `errormessage` VARCHAR(512) DEFAULT NULL,  `randno` INT(11) DEFAULT NULL,  PRIMARY KEY (`dbname`,`eventname`,`starttime`),  KEY `ix_endtime` (`endtime`),  KEY `ix_starttime_randno` (`starttime`,`randno`)) ENGINE=INNODB DEFAULT CHARSET=utf8;

2.event 建立模板

DELIMITER $$CREATE DEFINER=`root`@`localhost` EVENT `job_update_status` ON SCHEDULE #修改以下调度信息EVERY 5 minute STARTS now() ON COMPLETION PRESERVE ENABLE DO BEGIN        DECLARE r_code CHAR(5) DEFAULT '00000';        DECLARE r_msg TEXT;        DECLARE v_error INTEGER;        DECLARE v_starttime DATETIME DEFAULT NOW();        DECLARE v_randno INTEGER DEFAULT FLOOR(RAND()*100001);                INSERT INTO mysql.t_event_history (dbname,eventname,starttime,randno)         #修改下面的作业名（该作业的名称）        VALUES(DATABASE(),'job_update_status', v_starttime,v_randno);                      BEGIN                #异常处理段                DECLARE CONTINUE HANDLER FOR SQLEXCEPTION                  BEGIN                        SET  v_error = 1;                        GET DIAGNOSTICS CONDITION 1 r_code = RETURNED_SQLSTATE , r_msg = MESSAGE_TEXT;                END;                                #此处为实际调用的用户程序过程                CALL automaintain.pro_update_status();        END;                UPDATE mysql.t_event_history SET endtime=NOW(),issuccess=ISNULL(v_error),duration=TIMESTAMPDIFF(SECOND,starttime,NOW()), errormessage=CONCAT('error=',r_code,', message=',r_msg),randno=NULL WHERE starttime=v_starttime AND randno=v_randno;        END$$DELIMITER ;

3.查询event

select * from mysql.event;