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 EVENT job_del_expire_privileges ON SCHEDULE

#修改以下调度信息

EVERY 10 minute STARTS current_timestamp 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_del_expire_privileges', 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_del_expire_request();

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 ;