MySQL定时任务删除数据

版权声明:本文为博主原创文章,未经博主允许不得转载。http://blog.csdn.net/leytton https://blog.csdn.net/Leytton/article/details/52643875

前提:表中有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

本文出处:


展开阅读全文

没有更多推荐了,返回首页