MySQL定时任务删除数据

前提：表中有date(defualt CURRENT_TIMESTAMP)字段

基础语句：

查看定时事件线程是否打开： SHOW VARIABLES LIKE '%event_scheduler%';

打开定时事件线程：SET GLOBAL event_scheduler=1;

查看已有的定时事件任务：SHOW EVENTS;

需求1：每过5秒钟删除过期30分钟的数据

mysql语句(先删除原事件)：

DROP event IF EXISTS e_delete_wxauth;
CREATE EVENT e_delete_wxauth
ON SCHEDULE
EVERY 5 SECOND
DO
DELETE FROM weixin_auth WHERE DATE < DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 30 MINUTE);

需求2：每天0点删除已过期30分钟事件

mysql语句(先删除原事件)：

DROP event IF EXISTS e_delete_wxauth;
CREATE EVENT e_delete_wxauth
ON SCHEDULE
EVERY 1 DAY STARTS '2000-01-01 00:00:00'
DO
DELETE FROM weixin_auth WHERE DATE < DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 30 MINUTE);

(PS:STARTS只是从这时间起线程开始执行，比如说现在时间是2016-09-20 12:30:15执行此SQL语句，要等2016-09-21 00:00:00才开始生效)

需求3：每天3点更新金币排行榜

DELIMITER | 
CREATE	EVENT `e_updata_rank_share_gold`
ON SCHEDULE
EVERY 1 DAY STARTS '2000-01-01 03:00:00'
DO
BEGIN
    DELETE FROM rank_gold;
    INSERT INTO rank_gold SELECT `user_id`,`username`,`img`,SUM(gold_num) AS gold FROM `gold_log` LEFT JOIN USER ON user.id=user_id  WHERE gold_type=3 GROUP BY user_id ORDER BY gold DESC LIMIT 0,15;
END |
DELIMITER ;

周期：

YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

文献参考:

http://dev.mysql.com/doc/refman/5.7/en/create-event.html

http://blog.csdn.net/acmain_chm/article/details/7703870

本文出处:

CSDN博客地址 http://blog.csdn.net/leytton/article/details/52643875