永远温柔,永远知进退。
mysql定时任务执行得sql语句
1.查询事件是否开启on/off
SHOW VARIABLES LIKE ‘%sche%’;
2.开启权限,将off改成on
SET GLOBAL event_scheduler=1;
3.创建存储过程
sys_log 计算存储sys_ayalysis
CREATE PROCEDURE myproaya()
INSERT INTO sys_analysis(platform_name,service_name,uv,pv,DATETIME)
SELECT platform_code,service_code ,COUNT(DISTINCT company_code),COUNT(1),DATE_FORMAT(create_date,’%Y-%m-%d’) AS time1 FROM sys_log
GROUP BY DATE_FORMAT(create_date,’%Y-%m-%d’),platform_code,service_code HAVING time1 BETWEEN CONCAT((SELECT DATE_SUB(CURDATE(),INTERVAL +1 DAY))) AND CONCAT((SELECT CURRENT_DATE))
ORDER BY DATE_FORMAT(create_date,’%Y-%m-%d’) DESC
4.创建定时器
(1)1秒执行一次
CREATE EVENT IF NOT EXISTS eventJob
ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO CALL mypro();
(2)sys_log 计算存储sys_ayalysis 每天凌晨一点执行
CREATE EVENT IF NOT EXISTS eventaya
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
ON COMPLETION PRESERVE
DO CALL myproaya();
5.关闭事件任务
ALTER EVENT eventJob ON
COMPLETION PRESERVE DISABLE;
6.开启事件任务
ALTER EVENT eventJob ON
COMPLETION PRESERVE ENABLE;