MySQL数据库 Event 定时执行任务
时间：2021-07-01 10:21:17
帮助过：6人阅读
、建立存储过程供事件调用
delimiter//
drop procedure if exists middle_proce
//
create procedure middle_proce()
begin
DELETE FROM jg_bj_comit_log
WHERE comit_time
< SUBDATE(NOW(),INTERVAL
2 MONTH);
DELETE FROM jg_bj_order_create
WHERE created_on
< SUBDATE(NOW(),INTERVAL
3 MONTH);
DELETE FROM jg_bj_order_match
WHERE created_on
< SUBDATE(NOW(),INTERVAL
3 MONTH);
DELETE FROM jg_bj_order_cancel
WHERE created_on
< SUBDATE(NOW(),INTERVAL
3 MONTH);
DELETE FROM jg_bj_operate_arrive
WHERE created_on
< SUBDATE(NOW(),INTERVAL
3 MONTH);
DELETE FROM jg_bj_operate_depart
WHERE created_on
< SUBDATE(NOW(),INTERVAL
3 MONTH);
DELETE FROM jg_bj_operate_login
WHERE created_on
< SUBDATE(NOW(),INTERVAL
3 MONTH);
DELETE FROM jg_bj_operate_logout
WHERE created_on
< SUBDATE(NOW(),INTERVAL
3 MONTH);
DELETE FROM jg_bj_operate_pay
WHERE created_on
< SUBDATE(NOW(),INTERVAL
3 MONTH);
DELETE FROM jg_bj_position_driver
WHERE created_on
< SUBDATE(NOW(),INTERVAL
3 MONTH);
DELETE FROM jg_bj_position_vehicle
WHERE created_on
< SUBDATE(NOW(),INTERVAL
3 MONTH);
DELETE FROM jg_bj_rated_passenger
WHERE created_on
< SUBDATE(NOW(),INTERVAL
3 MONTH);
end//
delimiter;
#2、开启event(要使定时起作用，MySQL的常量GlOBAL event_schduleer 必须为on 或者1)
show variables like ‘event_scheduler‘
set global event_scheduler
=‘on‘
#3、创建Evnet事件
drop event
if exists middle_event;
create event middle_event
on schedule every
1 DAY STARTS
‘2017-12-05 00:00:01‘
on completion preserve ENABLE
do call middle_proce();
#4、开启Event 事件
alter event middle_event
on completion preserve enable;
#5、关闭Event 事件
alter event middle_event
on completion preserve disable;
MySQL数据库 Event 定时执行任务
标签：业务 begin int log mys mit match opera end
