4. 用mysql起一个定时任务执行存储过程

1. 建立存储过程

比如存储过程是，删除15天前的数据：

del_half_month_before_data:

DELIMITER //

BEGIN

DECLARE date_string varchar(100);

DECLARE  date_string2 varchar(100);

DECLARE  date_string3 varchar(100);

SELECT DATE_SUB(CURDATE(), INTERVAL 30 DAY) INTO date_string;

SELECT CONCAT(date_string," 00:00:00") INTO date_string2;

SELECT UNIX_TIMESTAMP(DATE(date_string2)) INTO date_string3;

DELETE FROM `mytable` WHERE `timestamp` < date_string3;

END//

查看刚才创建的存储过程：show create procedure del_half_month_before_data;

执行刚才创建的存储过程：call del_half_month_before_data();

2. 检查mysql定时器是否开 & 启停定时器 & 启停事件

查看event是否开启 : SHOW VARIABLES LIKE '%event_sche%'; 或 show variables like 'event_scheduler';

将事件计划开启 :SET GLOBAL event_scheduler = 1;  或 set global event_scheduler=1;

将事件计划关闭 :SET GLOBAL event_scheduler = 0; 或 set global event_scheduler=1;

关闭事件任务 :ALTER EVENT eventName ON COMPLETION PRESERVE DISABLE;

开启事件任务 :ALTER EVENT eventName ON COMPLETION PRESERVE ENABLE;

查看事件任务 : SHOW EVENTS;

3. 定时执行存储过程，比如每天凌晨1点执行

DROP EVENT IF EXISTS event_del_half_month_before_data;

CREATE EVENT event_del_half_month_before_data on schedule EVERY 1 DAY starts '2017-01-01 01:00:00' do call del_half_month_before_data();

####亲测有效，good####

参考：

1. mysql设置定时任务

2. http://blog.csdn.net/s445320/article/details/51897179?_t=t

3. http://www.cnblogs.com/chenpi/p/5137310.html

4. http://blog.sina.com.cn/s/blog_70207ec20101nzf2.html