创建测试表
代码如下:
create table t
(
v varchar(100) not null
) engine innodb default charset=utf8;
创建定时器 调用的存储过程
delimiter $$
drop procedure if exists e_test $$
create procedure e_test()
begin
insert into t values('1');
end $$
delimiter ;
要使定时起作用 mysql的常量global event_scheduler必须为on或者是1
-- 查看是否开启定时器
show variables like '%sche%';
-- 开启定时器 0：off 1：on
set global event_scheduler = 1;
-- 创建事件
--每隔一秒自动调用e_test()存储过程
create event if not exists event_test
on schedule every 1 second
on completion preserve
do call e_test();
-- 开启事件
alter event event_test on
completion preserve enable;
-- 关闭事件
alter event event_test on
completion preserve disable;
从现在开始每隔九天定时执行
create event event1
on schedule every 9 day starts now()
on completion preserve enable
do
begin
call total();
end
每个月的一号凌晨1 点执行
create event event2
on schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day), interval 1 month),interval 1 hour)
on completion preserve enable
do
begin
call stat();
end
每个季度一号的凌晨2点执行
create event total_season_event
on schedule every 1 quarter starts date_add(date_add(date( concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)),interval 1 quarter),interval 2 hour)
on completion preserve enable
do
begin
call season_stat();
end
每年1月1号凌晨四点执行
create event total_year_event
on schedule every 1 year starts date_add(date(concat(year(curdate()) + 1,'-',1,'-',1)),interval 4 hour)
on completion preserve enable
do
begin
call year_stat();
end
mysql的event在进行按月，季，年进行自动调用存储过程时，为了测试可以把系统改为年的最后一天，如2010-12-31 23：59：55;