mysql定时任务
这两天工作涉及到定时生成数据,记录一下。
因为不牵扯业务,基本上只有单表的数据变动,因此只需要mysql的定时任务就能满足需求。
mysql可视化工具(非必须):mysql workbench;
一, 是否开启定时任务
show variables like “%event_sche%”;
如图所示,value值为on的时候,数据库定时任务为开启状态。
若value值为off,数据库定时任务为关闭状态,则需要先将数据库定时任务开启。
set global event_scheduler=1;
二,创建存储过程
– 删除可能重名的存储过程
DROP PROCEDURE IF EXISTS waiter_procedure;
– 创建存储过程
delimiter $$
create procedure waiter_procedure()
begin
DECLARE v INT DEFAULT 0;--定义变量
select RAND()*30 into v;--变量赋值(随机数)
INSERT INTO `waiter_monitor_data`
(`waiter`, `shop_id`, `consult_num`, `pre_sale_consult_num`, `reception_num`,
`unanswered_num`, `response_time_all`, `response_num`, `response_num_all`, `response_time`,
`fast_response_num`, `consult_time`, `leave_msg_response_num`, `leave_msg_num`, `select_time`)
select
`waiter`, `shop_id`, `consult_num`, `pre_sale_consult_num`, `reception_num`,
`unanswered_num`, `response_time_all`, `response_num`, `response_num_all`, `response_time`,
`fast_response_num`, `consult_time`, `leave_msg_response_num`, `leave_msg_num`, ADDDATE(now(), -1) as `select_time`
from
`waiter_monitor_data`
where select_time between ADDDATE('2020-02-08 00:00:00', v) and ADDDATE('2020-02-08 23:59:59', v) ;
end$$
该存储过程的作用是查询随机日期并入库
为保证代码准确性,存储过程创建完毕后请先手动调用
手动调用存储过程call waiter_procedure();
显示存储过程show create procedure waiter_procedure;
三,创建事件
– 先删除事件
DROP EVENT IF EXISTS waiter_data_event ;
– 创建名字为waiter_data_event的事件
CREATE EVENT `waiter_data_event`
ON SCHEDULE EVERY 1 MINUTE STARTS DATE_ADD(now(), INTERVAL 0 MINUTE)
ON COMPLETION NOT PRESERVE
ENABLE
DO call waiter_procedure()
;
注: EVERY 1 MINUTE 即为一分钟调用一次,因此该定时任务一分钟执行一次,若想改为一天执行一次则需要如下方法设置(每天凌晨1点执行):
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 0 DAY), INTERVAL 1 HOUR);
四,开启定时任务
alter event waiter_data_event on completion preserve enable; -- 开启定时任务
alter event waiter_data_event on completion preserve disable; -- 关闭定时任务