MySQL Scheduler Overview with Examples [SOLVED]

MySQL Scheduler (also called Event Scheduler) lets you to schedule the tasks or events at a specified time. MySQL Event Scheduler could be very useful if you want to follow a specific schedule (predefined time or regular intervals) to execute one or more queries. When we create an Event, MySQL would be internally creating a named database object containing one or more SQL statements and a schedule. Theoretically, this is same as how we use Cron Expressions in Quartz Scheduler to run jobs/tasks at a specified interval.

Also Read : How to Update MySQL Collation for all tables in the schema

You can use the MySQL Scheduler to even run long running queries directly from the workbench. As it runs in the background, your workbench will not be blocked till the query completes its execution.

MySql Scheduler Examples

Below are few examples on how you can use the MySQL Scheduler. You can execute these commands from your MySQL workbench/Editor.

1. In this example, we are scheduling the execution of an Insert statement at a particular time.

SET global event_scheduler=ON

CREATE EVENT test_event1

ON SCHEDULE AT ‘2017-01-12 17:30:00’

INSERT INTO testdb.test_table VALUES(“Hello”)

2. In this example, we are executing a stored procedure instantly. We can use this for triggering any long running queries from the workbench.

SET GLOBAL event_scheduler = ON;

CREATE EVENT test_event

ON SCHEDULE AT CURRENT_TIMESTAMP

call test_stored_procedure();

3. In this example, the stored procedure gets executed once, exactly one day after the event is created.

SET global event_scheduler=ON

CREATE EVENT e_call_myproc

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY

call test_stored_procedure(13, 17, 22);

You can create, modify and drop the Scheduled events. After an event is created, we can modify the event by using ‘ALTER EVENT’ statement. When the event is no longer required, it can be dropped by using the ‘DROP EVENT’ statement.

Also Read: JaCoCo Code Coverage in Springboot Applications : A Comprehensive Guide

MySql Scheduler Examples

About Prasanna Manjunatha