mysql-Scheduled Tasks

Last week, I met a task that needed to be performed on a daily basis, before using Java to write a small program, because of the customer deployment time is uncertain, and I can remotely on the customer production environment database, so with the help of MySQL timing task to achieve.
        since MySQL5.1.6, a very distinctive feature has been added – Event Scheduler (Scheduler), which can be used to perform certain tasks on a timed basis (e.g. deleting records, summarizing data, etc.) instead of the original onlyOperating systemto perform the work of a scheduled task. It's worth mentioning thatMysqlthe event Scheduler can perform a task precisely every second, while the operating system's scheduled tasks such as:LinuxCron or Scheduled tasks under Windows) can only be performed once per minute. For some applications that require a higher level of real-time data (e.g., stocks, scores, etc.) it is very suitable.
A.commands related to timed tasks<textarea style="display: none">--To see if the event is open: Show VARIABLES like ' Event_scheduler ';--open event:set global Event_scheduler = 1;--Close Event:set global E Vent_scheduler = 0;--Close an event task: ALTER event eventName on completion PRESERVE disable;--Note: After the Shutdown Time Task command is restarted, after you restart the MySQL server, The event will be deleted (Beta version: 5.1.30)--Open an incident task: ALTER event eventName on completion PRESERVE enable;--Delete a time task: Drop event [IF EXISTS] event_name--viewing event tasks show events;</textarea>
Two. Create a scheduled Task 1. Simple example(starting from 2017-07-19 09:50:00, once every 10 seconds)    

1. DROP EVENT IF EXISTS e_fans_info_1; CREATE EVENT e_fans_info_1on SCHEDULE Every second starts TIMESTAMP ' 2017-07-19 09:50:00 ' on completion preservedoupdat E fans_info SET cust_servce_status=0 WHERE cust_servce_status = 1;
2. Syntax CREATE EVENT [ifnot EXISTS] Event_nameonschedule schedule[oncompletion [NOT] preserve][enable | Disable][comment ' COMMENT ']do sql_statement;schedule:at TIMESTAMP [+ INTERVAL INTERVAL] | Every INTERVAL [starts TIMESTAMP] [ENDS TIMESTAMP] interval:quantity {year | QUARTER | MONTH | Day | HOUR |　　 MINUTE | WEEK | SECOND | Year_month | Day_hour |　　 Day_minute | Day_second | Hour_minute | Hour_second | Minute_second}

three. Frequently Asked Questions

2. On completion PRESERVE and On completion not PRESERVE difference?
   

   When preserve is on completion, when the event expires, the event will be disable, but the event will still exist
   When completion not preserve is on, the event is automatically deleted when the event expires.

3. In  event events: On SCHEDULE Scheduled Tasks, there are two ways to set up scheduled tasks:   
   1. At timestamp, used to complete a single scheduled task. 
   2. Every time unit [starts timestamp] [ends timestamp], which is used to complete a recurring scheduled task. In both scheduled tasks, the timestamp can be any of the timestamp and datetime data types, and the timestamp needs to be greater than the current time. 
   in a recurring scheduled task, the number of times (units) can be an integer of any non-null (NOT NULL), the time unit is the keyword: year,month,day,hour,minute or second. 

5. What is the difference between a time dispatcher and a trigger (Triggers)? The  Event Scheduler can sometimes also be called a temporary trigger (temporal triggers) because the event scheduler performs certain tasks based on a specific time-period trigger, and triggers are triggered based on events generated by a table. 
   

Null

mysql-Scheduled Tasks