php创建mysql计划任务_MySQL的计划任务创建

实际项目中只想将最近7天的记录保存在MySQL数据库中，使用程序通过SQL指令的方式删除比较麻烦且效率低，用Mysql 提供的事件调度器

实际项目中只想将最近7天的记录保存在MySQL数据库中，使用程序通过SQL指令的方式删除比较麻烦且效率低，，用Mysql 提供的事件调度器(event scheduler)可轻松实现。

具体步骤如下：

1：超级用户方式登陆MySQL console

# mysql　-uroot

2：打开event_scheduler(默认是关掉的)

mysql> set global event_scheduler = ON;

3：创建我们的事件(本例中命名为delete_old_record)

mysql> CREATE EVENT delete_old_record

ON SCHEDULE EVERY 1 DAY STARTS NOW()

DO

-- delete the old records of demo_1_table

DELETE FROM demo_1_table WHERE datediff(NOW(),log_timestamp)>=7;

-- delete the old records of puma_2_table

DELETE FROM demo_2_table WHERE datediff(NOW(),log_timestamp)>=7;

-- delete the old records of puma_3_table

DELETE FROM demo_3_table WHERE datediff(NOW(),log_timestamp)>=7;

-- delete the old records of puma_4_table

DELETE FROM demo_4_table WHERE datediff(NOW(),log_timestamp)>=7;

4：启动创建的事件

mysql> ALTER EVENT delete_old_record ENABLE;

这样以后，数据库就会每天执行DO后面的作业，删除各个table中7天之前的记录。

附录：CREATE EVENT的格式如下：

CREATE

[DEFINER = { user | CURRENT_USER }]

EVENT

[IF NOT EXISTS]

event_name

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE | DISABLE ON SLAVE]

[COMMENT 'comment']

DO event_body;

schedule:

AT timestamp [+ INTERVAL interval] ...

| EVERY interval

[STARTS timestamp [+ INTERVAL interval] ...]

[ENDS timestamp [+ INTERVAL interval] ...]

interval:

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

本文原创发布php中文网，转载请注明出处，感谢您的尊重！