MySQL Event Scheduler

Introduction

The MySQL Event Scheduler manages the scheduling and execution of events, that is, tasks that run according to a schedule.
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.

Event Scheduler Configuration 

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

1) OFF: The Event Scheduler is stopped. The event scheduler thread does not run, is not shown in the output of SHOW PROCESSLIST, and no scheduled events are executed. OFF is the default value for event_scheduler.

2) ON: The Event Scheduler is started; the event scheduler thread runs and executes all scheduled events.
When the Event Scheduler is ON, the event scheduler thread is listed in the output of SHOW PROCESSLIST as a daemon process.

3) DISABLED: This value renders the Event Scheduler nonoperational. When the Event Scheduler is DISABLED, the event scheduler thread does not run (and so does not appear in the output of SHOW PROCESSLIST). In addition, the Event Scheduler state cannot be changed at runtime.

If the Event Scheduler status has not been set to DISABLED, event_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;

CREATE EVENT Syntax

The minimum requirements for a valid CREATE EVENT statement are as follows:
- The keywords CREATE EVENT plus an event name, which uniquely identifies the event in a database schema.
- An ON SCHEDULE clause, which determines when and how often the event executes.
- A DO clause, which contains the SQL statement to be executed by an event.

EXAMPLE :-

Create event for specific date and time

CREATE EVENT event_update_data
ON SCHEDULE AT '2019-09-09 23:59:00'
DO update table_1 as a join table_2 as b on a.unique_id=b.unique_id set a.column_name=b.column_name ;

In above example we have created event named update_data which will get executed on 2019-09-09 23:59:00 and execute join query specified after do clause.

Create event for every hour

CREATE EVENT event_clear_data
ON SCHEDULE  EVERY 1 HOUR
COMMENT 'Clears out sessions table each hour.'
DO  DELETE FROM site_activity.sessions;

In above example we have created event named event_clear_data which will get executed hourly and execute delete query specified after do clause.

Create event for every day

		
delimiter |

CREATE EVENT event_archive_data
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'archive data from log table'
    DO
      BEGIN
        INSERT INTO tbl_data_archive 
          SELECT *
            FROM tbl_data where createdate!=curdate();
        DELETE FROM tbl_data where createdate!=curdate() ;
      END |

delimiter ;

you can use compound-statement syntax in the DO clause by using the BEGIN and END keywords as shown in above example.
In above example we have created event named event_archive_data which will get executed every day and execute queries specified between begin and end clauses.

References :-

MySQL - Using the Event Scheduler

Support us by sharing our content :-

Facebook

LinkedIn

Support us on Patreon :-

Become Patron