MySQL Stored procedure timed task

First, the basic concept

The mysql5.1 version begins with the introduction of the event concept. Event is not only a "time trigger", unlike the events triggered by the triggers, the event is similar to a Linux crontab scheduled task for time triggering. Trigger a related SQL statement or stored procedure at a specific point in time by using a separate or called stored procedure.

ii. Scope of application

For operations that have fixed requirements at regular intervals, such as creating tables, deleting data, and so on, you can use event to handle it.
For example, use the event to automatically create three tables to be used next month on 1st 1 o'clock in the morning of the month.

Third, the use of rights

When invoking an SQL statement using event alone, viewing and creating requires the user to have the event permission, and when the SQL statement is called, the user is required to have permission to execute that SQL. The settings for the event permission are saved in the Event_priv field of the Mysql.user table and the Mysql.db table.
When event and procedure are used together, viewing and creating stored procedures requires the user to have the Create routine permission, and the call to the stored procedure executes with the Excute permission, which requires the user to have permission to execute the SQL statement when the stored procedure invokes it. There are several types of view event commands:

(1) Inquiry mysql.event form;
(2) through the show events command;
(3) through the show full events command;
(4) by querying the Information_schema.events table
(5) SHOW CREATE EVENT. In summary, the low frequency of event usage is recommended for root user creation and maintenance.

Iv. Basic Grammar

4.1 Turn on Timer

For the event to work, the MySQL constant global event_scheduler must be on or 1.

--Check whether the timer is turned on

' Event_scheduler ';

--Open timer 0:off 1:on

1

When you set the event plan to 0 or off, that is, when the event planning process is closed, no new events are executed, but the existing running events are executed to completion

For our online environment, when using the event, notice that the timer is turned on in the main library, the timer is turned off from the library, the event trigger all operations are recorded binlog for master-slave synchronization, and the timer opening from the library is likely to cause the card library. After switching the main library, remember to turn on the timer on the new Main library.
Please pay special attention!

4.2 Creating events

The syntax for CREATE EVENT is as follows:

CREATE EVENT [IF not EXISTS]---------------------------------------------* Callout 1

Event_Name-----------------------------------------------------* Callout 2
On SCHEDULE SCHEDULE------------------------------------* Callout 3

[on completion [NOT] PRESERVE]-----------------* Callout 4

[ENABLE | DISABLE]----------------------------------------* Callout 5

[COMMENT ' COMMENT ']--------------------------------------* Callout 6

Do sql_statement-----------------------------------------------* Callout 7

Span>
 
[ If not EXISTS]
 
Using if not EXISTS is only created if an event with the same name does not exist, otherwise it is ignored. It is not recommended to ensure that event creation succeeds.

callout 2: event_name
 
Name maximum length can be 64 bytes. The name must be unique in the current dateabase, and the same database cannot have an event with the same name.

Using event is a common work of creating tables, inserting data, deleting data, emptying tables, and deleting tables.

To avoid the inconvenience of naming the specification, it is best to have the event name have the ability to describe the entire event. The recommended naming rules are: Action name _ (into/from_) Table name _time, for example:

1. Create (empty/delete) Fans table every month:

Create (truncate/drop) _table_fans_month;

2. Insert (delete) data from fans table daily:

Insert (Delete) _into (from) _fans_day;

Callout 3: On SCHEDULE

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.

Hint: Other time units are also legal such as: QUARTER, WEEK, Year_month,day_hour,day_minute,day_second,hour_minute,hour_second, Minute_second, It is not recommended to use these non-standard time units.

Callout 4: [on completion [NOT] PRESERVE] The on completion parameter means "When this event does not recur", that is, when a single scheduled task is completed or when a recurring scheduled task is executed to the ends stage. and preserve's role is to make the event run out

Will not be dropped after completion, it is recommended to use this parameter, in order to view the event specific information.

Callout 5:[ENABLE | The DISABLE] parameter enable and DISABLE indicate the state of the set event. Enable indicates that the system will execute this event. Disable indicates that the system does not execute the event.

The event can be closed or turned on with the following command:

ALTER EVENT Event_Name enable/disable

Callout 6:[COMMENT ' COMMENT '] note appears in the metadata, which is stored in the COMMENT column of the Information_schema table, The maximum length is 64 bytes. ' Comment ' means placing comments between single quotes, and it is recommended to use annotations to express more comprehensive information.

Callout 7: do sql_statement The Do sql_statement field represents the SQL statement or stored procedure that the event needs to execute. The SQL statement here can be a compound statement, for example:

BEGIN CREATE TABLE test1; // Create a table (need to test a bit) DROP TABLE test2; // Delete Table call Proc_test1 (); //

Call the stored procedure END

Use the Begin and end identifiers to place the composite SQL statements between the execution order. Of course, the SQL statement is limited, and its limitations are the same as those in function functions and trigger trigger for SQL statements, if you cannot use some of the functions in function and trigger trigger

SQL, which is also not available in the event. Clearly, there are several:

CREATEALTERLOAD DATA

4.3 Execution logic

For (events created each event this has been created)

If (the state of the event is not disable)

and (current time before ends time)

and (the current time is after starts time)

and (elapsed time since last execution)

and (not executed)

Then: Create a new thread

Pass the SQL statement of the event to the new thread

(The thread will close automatically when it finishes executing)
4.4 Modification Events use ALTER event to modify the event, with the specific alter syntax as follows, similar to the syntax for creating an event:

ALTER EVENT Event_Name  on  [RENAME to New_event_name][incompletion [not]   [ENABLE | DISABLE][COMMENT ' COMMENT '] do sql_statement

4.5 Deleting Events Event using the Drop EVENT statement to delete the events that have been created, the syntax is as follows:

DROP[IF EXISTS]  event_name

However, when an event is running, deleting the event does not cause the event to stop, and the event is executed until it finishes. Using the drop user and drop DATABASE statements will also delete the events contained therein.

V. Examples of common use
Automatically calls the e_test () stored procedure every second

CREATE IF  not EXISTS e_test  on 1 SECOND  on Completion Preservedo call E_test ();

One 1 o'clock in the morning per month performs a stat () stored procedure:

CREATEEVENT not EXISTSSTAT onSCHEDULE every1  MONTHStarts Date_add (Date_add (Date_sub (Curdate (), INTERVAL Day(Curdate ())-1  Day), INTERVAL1 MONTH), INTERVAL1HOUR) onCompletion PRESERVE EnabledoBEGINCall STAT ();END

V. Self-written examples

Delimiter// UseTest//Drop Table if existsDemo//Create TableDemo (CIDint, Createdtimedatetime) //Drop procedure if existsInsertData//Create procedureInsertData ()begin     DeclareHowmanytimesint; DeclareCounterint default  the; Lable: while(3<=4) doSetCounter=Counter+1; Insert   intoDemo (cid,createdtime)Values(Counter,now ()); ifCounter/5= -  Thenleave lable; End if; End  while;End //DropEventif existsE_insertdata//CreateEvent E_insertdata onSchedule every -Second onCompletion Preservedo call InsertData ()//

Transferred from: http://www.jb51.net/article/38319.htm