mysql的event定时任务_Mysql定时任务（event）

-- 检查Mysql是否开启了event

show variables like '%sc%';

-- 手动打开

set global event_scheduler=on;

-- 显示

show events;

-- 测试表

drop table if exists test ;

create table test (id int auto_increment primary key,time timestamp);

truncate table test;

select * from test;

-- 存储过程

drop procedure if exists test_insert;

create procedure test_insert()

begin

insert into test(time) values(now());

end;

call test_insert();

-- 定时任务

create event if not exists e_test

on schedule every 1 minute

on completion preserve

do call test_insert();

-- 关闭

ALTER EVENT e_test DISABLE;

-- 开启

ALTER EVENT e_test ENABLE;

-- 删除

DROP EVENT IF EXISTS e_test;

-- 语法

CREATE EVENT [IF NOT EXISTS] event_name

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE]

[COMMENT 'comment']

DO sql_statement;

schedule:

AT TIMESTAMP [+ INTERVAL INTERVAL]

| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]

INTERVAL:

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

oncompletion preserve和oncompletion not preserve

当任务的计划不是一秒执行一次【比如3 minute一次】的时候，设置preserve计划启动的时候受影响的行数为1行，not preserve的时候全部行数；

如果存储过程中查到10条数据，设置preserve，一次只处理一条，如果not preserve查到多少，处理多少~