mysql定时任务语句

										永远温柔，永远知进退。

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;