MySQL定时任务相关操作 2017年2月5日22:43:16 发表评论 3,034 MySQL在5.1.6版本之后添加了事件调度器（Event Scheduler）,可以用来MySQL定时执行一些数据库功能，比如数据的增、删、改、查，又比如在某一特定的时刻清空某一张表等等，可以设置事件执行的周期开始时间和结束时间等等，这一功能大大增强的数据库的功能。下面以特定的实例来简单介绍一下该功能的用法。 一、开启事件功能 查看是否开启了MySQL事件功能: [cce_mysql]show variables like 'event_scheduler';[/cce_mysql] 1 [cce_mysql]show variables like 'event_scheduler';[/cce_mysql] 若显示结果状态为ON或者1则为开启，OFF或0为关闭。 开启语句：set global event_scheduler=1;或者on</fieldset> 1 开启语句：set global event_scheduler=1;或者on</fieldset> 二、创建事件 基本语法： [cce_mysql] create event [ifnot exits] event_name on schedule schedule [oncompletion [not] preserve] [enable| disable] [comment 'comment'] do sql_statement; [/cce_mysql] 12345678 [cce_mysql]create event [ifnot exits] event_name on schedule schedule [oncompletion [not] preserve] [enable| disable] [comment 'comment'] do sql_statement;[/cce_mysql] 实例1：每5秒执行一次sql语句 [cce_mysql] create event test_event on schedule every 5 second do [sql语句]; [/cce_mysql] 12345 [cce_mysql]create event test_event on schedule every 5 second do [sql语句];[/cce_mysql] 实例2：5天后执行某sql语句 [cce_mysql] create event test_event on schedule at current_timestamp + interval 5 day do [sql语句]; [/cce_mysql] 12345 [cce_mysql]create event test_event on schedule at current_timestamp + interval 5 day do [sql语句];[/cce_mysql] 实例3：在2017年2月5号12：00：00执行 [cce_mysql] create event test_event on schedule at timestamp '2017-02-05 12:00:00' do [sql语句]; [/cce_mysql] 12345 [cce_mysql]create event test_event on schedule at timestamp '2017-02-05 12:00:00' do [sql语句];[/cce_mysql] 实例4：每天执行 [cce_mysql] create event test_event on schedule every 1 day do [sql语句]; [/cce_mysql] 12345 [cce_mysql]create event test_event on schedule every 1 day do [sql语句];[/cce_mysql] 实例5：5天后开启每天执行 [cce_mysql] create event test_event on schedule every 1 day starts current_timestmp + interval 5day do [sql语句]; [/cce_mysql] 123456 [cce_mysql]create event test_event on schedule every 1 day starts current_timestmp + interval 5day do [sql语句];[/cce_mysql] 实例6：每天执行，五天后停止执行 [cce_mysql] create event test_event on schedule every 1 day ends current_timestmp + interval 5 day do [sql语句]; [/cce_mysql] 123456 [cce_mysql]create event test_event on schedule every 1 day ends current_timestmp + interval 5 day do [sql语句];[/cce_mysql] 实例7：5天后开启每天执行，一个月后停止 [cce_mysql] create event test_event on schedule every 1 day starts current_timestmp + interval 5 day ends current_timestmp + interval 1 month do [sql语句]; [/cce_mysql] 1234567 [cce_mysql]create event test_event on schedule every 1 day starts current_timestmp + interval 5 day ends current_timestmp + interval 1 month do [sql语句];[/cce_mysql] 实例8：定时执行一次或者持久执行 [cce_mysql] create event test_event on schedule every 1 day oncompletion not preserve //去掉not为持久执行 do [sql语句]; [/cce_mysql] 123456 [cce_mysql]create event test_event on schedule every 1 day oncompletion not preserve //去掉not为持久执行 do [sql语句];[/cce_mysql] 三、修改事件 基本语法： [cce_mysql] ALERT EVENT event_name [ONSCHEDULE schedule] [RENAME TOnew_event_name] [ON COMPLETION [NOT] PRESERVE] [COMMENT 'comment'] [ENABLE | DISABLE] [DO sql_statement]; [/cce_mysql] 123456789 [cce_mysql]ALERT EVENT event_name [ONSCHEDULE schedule] [RENAME TOnew_event_name] [ON COMPLETION [NOT] PRESERVE] [COMMENT 'comment'] [ENABLE | DISABLE] [DO sql_statement];[/cce_mysql] 例子：将每天情况test表改为5天情况一次 [cce_mysql] ALTER EVENT test_event ON SCHEDULE EVERY 5 DAY; [/cce_mysql] 1234 [cce_mysql]ALTER EVENT test_event ON SCHEDULE EVERY 5 DAY;[/cce_mysql] 四、设置MySQL事件参数 1、临时关闭事件 [cce_mysql] ALTER EVENT test_event DISABLE; 注意：执行之后重启数据库之后该事件会被删除！ [/cce_mysql] 1234 [cce_mysql]ALTER EVENT test_event DISABLE;注意：执行之后重启数据库之后该事件会被删除！[/cce_mysql] 2、开启事件 [cce_mysql]ALTER EVENT test_event ENABLE[/cce_mysql] 1 [cce_mysql]ALTER EVENT test_event ENABLE[/cce_mysql] 五、删除事件 基本语法： [cce_mysql]DROP EVENT [IF EXISTS] event_name;[/cce_mysql] 1 [cce_mysql]DROP EVENT [IF EXISTS] event_name;[/cce_mysql] 六、查看已创建的MySQL事件信息 1.查看所有事件 [cce_mysql]show events;[/cce_mysql] 1 [cce_mysql]show events;[/cce_mysql] 2.查看事件的创建信息 [cce_mysql]show create event [事件名];[/cce_mysql] 1 [cce_mysql]show create event [事件名];[/cce_mysql] 收 藏 赞 0 赏 分享
