mysql event schedule_MySQL Schedule Event

建立事件历史日志表

-- 用于查看事件执行时间等信息

create table t_event_history  (

dbname  varchar(128) not null default '',

eventname  varchar(128) not null default '',

starttime  datetime(3) not null default '1000-01-01 00:00:00',

endtime  datetime(3) default null,

issuccess  int(11) default null,

duration  int(11) default null,

errormessage  varchar(512) default null,

randno  int(11) default null

);

为每个并发线程创建一个事件

delimiter //

create event ev1 on schedule at current_timestamp + interval 1 hour on completion preserve disable do

begin

declare r_code char(5) default '00000';

declare r_msg text;

declare v_error integer;

declare v_starttime datetime default now(3);

declare v_randno integer default floor(rand()*100001);

insert into t_event_history (dbname,eventname,starttime,randno)

#作业名

values(database(),'ev1', 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 sp_unique(1);

end;

update t_event_history set endtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno;

end

//

create event ev2 on schedule at current_timestamp + interval 1 hour on completion preserve disable do

begin

declare r_code char(5) default '00000';

declare r_msg text;

declare v_error integer;

declare v_starttime datetime default now(3);

declare v_randno integer default floor(rand()*100001);

insert into t_event_history (dbname,eventname,starttime,randno)

#作业名

values(database(),'ev2', 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 sp_unique(2);

end;

update t_event_history set endtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno;

end

//

create event ev3 on schedule at current_timestamp + interval 1 hour on completion preserve disable do

begin

declare r_code char(5) default '00000';

declare r_msg text;

declare v_error integer;

declare v_starttime datetime default now(3);

declare v_randno integer default floor(rand()*100001);

insert into t_event_history (dbname,eventname,starttime,randno)

#作业名

values(database(),'ev3', 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 sp_unique(3);

end;

update t_event_history set endtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno;

end

//

create event ev4 on schedule at current_timestamp + interval 1 hour on completion preserve disable do

begin

declare r_code char(5) default '00000';

declare r_msg text;

declare v_error integer;

declare v_starttime datetime default now(3);

declare v_randno integer default floor(rand()*100001);

insert into t_event_history (dbname,eventname,starttime,randno)

#作业名

values(database(http://www.my516.com),'ev4', 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 sp_unique(4);

end;

update t_event_history set endtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno;

end

---------------------