mysql有计划任务_使用mysql的计划任务

In this tutorial, you will learn about MySQL event scheduler and how to create MySQL events to automate database tasks.

A MySQL event is a task that runs based on a predefined schedule therefore sometimes it is referred to as a scheduled event. MySQL event is also known as “temporal trigger” because it is triggered by time, not by table update like a trigger. A MySQL event is similar to a cron job in UNIX or a task scheduler in Windows.

You can use MySQL events in many cases such as optimizing database tables, cleaning up logs, archiving data, or generate complex reports during off-peak time.

MySQL event scheduler configuration

MySQL uses a special thread called event schedule thread to execute all scheduled events. You can see the status of event scheduler thread by executing the following command:SHOWPROCESSLIST;

By default, the event scheduler thread is not enabled. To enable and start the event scheduler thread, you need to execute the following command:SETGLOBALevent_scheduler=ON;

Now to see the status of event scheduler thread, you execute theSHOW PROCESSLISTcommand again.SHOWPROCESSLIST;

To disable and stop the event the event scheduler thread, you execute the SET GLOBAL command with value of the event_scheduler is OFF:SETGLOBALevent_scheduler=OFF;

Creating new MySQL events

Creating an event is similar to creating other database objects such as stored procedures or triggers. An event is a named object that contains SQL statements.

A stored procedure is only executed when it is invoked directly; a trigger is executed when an event associated with a table such asinsert,update, ordeleteevent occurs, while an event can be executed at once or more regular intervals.

To create and schedule a new event, you use theCREATE EVENTstatement as follows:CREATEEVENT[IFNOTEXIST]event_nameONSCHEDULEscheduleDOevent_body

Let’s examine the statement in more detail.First, you specify the event name after theCREATE EVENTclause. The event name must be unique within a database schema.

Second, you put a schedule after theON SCHEDULEclause. If the event is a one-time event, you use the syntax:AT timestamp [+ INTERVAL]. If the event is a recurring event, you use theEVERYclause:EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL].

Third, you place the SQL statements after theDOkeyword. It is important to notice that you can call a stored procedure inside the body of the event. In case you have compound SQL statements, you can wrap them in aBEGIN ENDblock.

Let’s look at few examples of creating events to understand the syntax above.

To create and schedule a new one-time event that inserts a message into a table called messages you do the following steps:.

First, create a new table namedmessagesby using theCREATE TABLEstatement as follows:CREATETABLEIFNOTEXISTSmessages(idINTPRIMARYKEYAUTO_INCREMENT,messageVARCHAR(255)NOTNULL,created_atDATETIMENOTNULL);

Second, create an event by using theCREATE EVENTstatement:CREATEEVENTIFNOTEXISTStest_event_01ONSCHEDULEATCURRENT_TIMESTAMPDOINSERTINTOmessages(message,created_at)VALUES('TestMySQLEvent1',NOW());

Third, check themessagestable; you will see that we have 1 record. It means the event was executed when it is created.SELECT*FROMmessages;

To shows all events of a database schema, you use the following statement:SHOWEVENTSFROMclassicmodels;

We don’t see any row returned because an event is automatically dropped when it is expired. In our case, it is one-time event and expired when its execution completed.

To change this behavior, you can use theON COMPLETION PRESERVEclause. The following statement creates another one-time event that is executed after its creation time 1 minute and not dropped after execution.CREATEEVENTtest_event_02ONSCHEDULEATCURRENT_TIMESTAMP+INTERVAL1MINUTEONCOMPLETIONPRESERVEDOINSERTINTOmessages(message,created_at)VALUES('TestMySQLEvent2',NOW());

Wait for 1 minute, check the messages table, another record was added:SELECT*FROMmessages;

If we execute theSHOW EVENTSstatement again, we see the event is there because the effect of theON COMPLETION PRESERVEclause:SHOWEVENTSFROMclassicmodels;

The following statement creates a recurring event that executes every minute and is expired in 1 hour from its creation time:CREATEEVENTtest_event_03ONSCHEDULEEVERY1MINUTESTARTSCURRENT_TIMESTAMPENDSCURRENT_TIMESTAMP+INTERVAL1HOURDOINSERTINTOmessages(message,created_at)VALUES('TestMySQLrecurringEvent',NOW());

Notice that we usedSTARTSandENDSclauses to define expiration period for the event. You can test this recurring event by waiting for few minutes and check themessagestable.SELECT*FROMmessages;

Drop MySQL events

To remove an existing event, you use theDROP EVENTstatement as follows:DROPEVENT[IFEXIST]event_name;

For example, to drop thetest_event_03event, you use the following statement:DROPEVENT[IFEXIST]test_event_03;

In this tutorial, you have learned about MySQL events, how to create and delete events from a database schema. In the next tutorial, we will show you how to change existing event.

Related TutorialsCreate Trigger in MySQL

Modifying MySQL Events