無料PHPプログラム

MySQL Events are tasks that run according to a schedule. Therefore, we sometimes refer to them as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a 「cron job」) or the Windows Task Scheduler.

Scheduled tasks of this type are also sometimes known as 「temporal triggers」, implying that these are objects that are triggered by the passage of time. While this is essentially correct, we prefer to use the term events in order to avoid confusion with triggers of the type discussed in 章?18. トリガ. Events should more specifically not be confused with 「temporary triggers」. Whereas a trigger is a database object whose statements are executed in response to a specific type of event that occurs on a given table, a (scheduled) event is an object whose statements are executed in response to the passage of a specified time interval.

While there is no provision in the SQL Standard for event scheduling, there are precedents in other database systems, and you may notice some similarities between these implementations and that found in the MySQL Server.

MySQL Events have the following major features and properties:

Events are executed by a special event scheduler thread; when we refer to the Event Scheduler, we actually refer to this thread. When running, the event scheduler thread and its current state can be seen by users having the SUPER privilege in the output of SHOW PROCESSLIST, as shown in the discussion that follows.

The global variable event_scheduler determines whether the Event Scheduler is enabled and running on the server. Beginning with MySQL 5.1.12, it has one of these 3 values, which affect event scheduling as described here:

When the server is runningevent_scheduler can be toggled between ON and OFF (using SET). It is also possible to use 0 for OFF, and 1 for ON when setting this variable. Thus, any of the following 4 statements can be used in the mysql client to turn on the Event Scheduler:

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

Similarly, any of these 4 statements can be used to turn off the Event Scheduler:

SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;

Although ON and OFF have numeric equivalents, the value displayed for event_scheduler by SELECT or SHOW VARIABLES is always one of OFF, ON, or DISABLED. DISABLED has no numeric equivalent. For this reason, ON and OFF are usually preferred over 1 and 0 when setting this variable.

Note that attempting to set event_scheduler without specifying it as a global variable causes an error:

mysql< SET @@event_scheduler = OFF;
ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL
variable and should be set with SET GLOBAL

Important: It is not possible to enable or disable the Event Scheduler when the server is running. That is, you can change the value of event_scheduler to DISABLED ? or from DISABLED to one of the other permitted values for this option ? only when the server is stopped. Attempting to do so when the server is running fails with an error.

To disable the event scheduler, use one of the following two methods:

To enable the Event Scheduler, restart the server without the --event-scheduler=DISABLED command line option, or after removing or commenting out the line containing event_scheduler=DISABLED in the server configuration file, as appropriate. Alternatively, you can use ON (or 1) or OFF (or 0) in place of the DISABLED value when starting the server.

Note: You can issue event-manipulation statements when event_scheduler is set to DISABLED. No warnings or errors are generated in such cases (provided that the statements are themselves valid). However, scheduled events cannot execute until this variable is set to ON (or 1). Once this has been done, the event scheduler thread executes all events whose scheduling conditions are satisfied.

In MySQL 5.1.11, event_scheduler behaved as follows: this variable could take one of the values 0 (or OFF), 1 (or ON), or 2. Setting it to 0 turned event scheduling off, so that the event scheduler thread did not run; the event_scheduler variable could not be set to this value while the server was running. Setting it to 1 so that the event scheduler thread ran and executed scheduled events. In this state, the event scheduler thread appeared to be sleeping when viewed with SHOW PROCESSLIST. When event_scheduler was set to 2 (which was the default value), the Event Scheduler was considered to be 「suspended」; the event scheduler thread ran and could be seen in the output of SHOW PROCESSLIST (where Suspended was displayed in the State column), but did not execute any scheduled events. The value of event_scheduler could be changed only between 1 (or ON) and 2 while the server was running. Setting it to 0 (or OFF) required a server restart, as did changing its value from 0 (or OFF) to 1 (or ON) or 2.

Prior to MySQL 5.1.11, event_scheduler could take one of only the 2 values 0|OFF or 1|ON, and the default value was 0|OFF. It was also possible to start and stop the event scheduler thread while the MySQL server was running.

For more information concerning the reasons for these changes in behaviour, see Bug#17619.

For SQL statements used to create, alter, and drop events, see 項19.2. 「Event Scheduler Syntax」.

MySQL 5.1.6 and later provides an EVENTS table in the INFORMATION_SCHEMA database. This table can be queried to obtian information about scheduled events which have been defined on the server. See 項19.3. 「Event Metadata」, and 項21.20. 「INFORMATION_SCHEMA EVENTS テーブル」, for more information.

For information regarding event scheduling and the MySQL privilege system, see 項19.5. 「The Event Scheduler and MySQL Privileges」.