MySQL event,作业,事件调度

 

 

创建表记录event执行过程

CREATE TABLE `mysql`.`event_history` (
  `dbname` VARCHAR(128) NOT NULL DEFAULT '',
  `eventname` VARCHAR(128) NOT NULL DEFAULT '',
  `starttime` DATETIME NOT NULL DEFAULT '0000-01-01 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 `idx_endtime` (`endtime`),
  KEY `idx_starttime_randno` (`starttime`,`randno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 collate utf8mb4_general_ci;

 

Example

1:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` EVENT `event1`
#修改以下调度信息
ON SCHEDULE EVERY 1 DAY STARTS '2014-01-03 01:00:00' 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.event_history (dbname,eventname,starttime,randno) VALUES (DATABASE(),'event1', 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 test.usp_test1();
    END;
    
    UPDATE mysql.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 ;

 

2:

create table tbl_v(
id int not null auto_increment,
user varchar(20) collate utf8mb4_general_ci default '',
createTime datetime default now(),
primary key (id)
)engine=innodb auto_increment default charset=utf8mb4 collate utf8mb4_general_ci;

create event if not exists event_1
on schedule every 5 second
on completion perserve
do
    insert into tbl_v (user) values ('uiop');

 

3:

delimiter //
drop procedure if exists procedure_flush_hosts //
create procedure procedure_flush_hosts()
begin
    flush hosts;
end //
delimiter ;

drop event is exists event_flush_hosts;
create event event_flush_hosts
on schedule every 120 second
on completion preserve disabled
do call procedure_flush_hosts();

 

 

 

开启event

alter event event_1 on completion preserve enable;

关闭event

alter event event_1 on completion preserve disable;