MySQL Event Scheduler: Concepts, Operations, and Practical Examples

MySQL events, also known as the event scheduler, are automated database operations that execute at specific times, allowing tasks such as data updates, inserts, deletions, backups, and clean‑ups without manual intervention.

Advantages : automation of repetitive jobs, flexible scheduling of execution time and frequency, and improved efficiency by running heavy tasks during off‑peak periods.

Common operations include checking whether the scheduler is enabled, turning it on or off, viewing existing events, and creating, altering, or dropping events. Example commands:

show variables like '%event_scheduler%';

set global event_scheduler = ON;

set global event_scheduler = OFF;

To permanently enable the scheduler, add event_scheduler=ON to the my.ini configuration file and restart MySQL.

Viewing events can be done with:

show events;

select * from information_schema.events;

Creating an event uses the CREATE EVENT statement. The basic syntax is:

CREATE [DEFINER = user] EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule_body [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment'] DO event_body;

Key clauses:

definer : optional user that checks permissions when the event runs.

IF NOT EXISTS : prevents errors if the event already exists.

event_name : unique identifier within the database.

ON SCHEDULE schedule_body : defines when and how often the event runs.

ON COMPLETION [NOT] PRESERVE : determines whether the event repeats (default is one‑time).

ENABLE | DISABLE | DISABLE ON SLAVE : controls the event’s active state.

COMMENT : optional description.

DO event_body : the SQL statements executed by the event; multiple statements can be wrapped in BEGIN … END .

The schedule_body can be a one‑time AT timestamp or a recurring EVERY interval with optional STARTS and ENDS clauses. Example interval syntax:

EVERY 30 MINUTE

EVERY 1 HOUR STARTS '2024-01-03 18:00:00'

EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK

Practical example – generating real‑time sales data : The following script creates a table face_sales_data and an event that runs every minute to insert random sales records.

DROP TABLE IF EXISTS sql_test1.face_sales_data;

CREATE TABLE IF NOT EXISTS sql_test1.face_sales_data (sales_date DATE COMMENT '销售日期', order_code VARCHAR(255) COMMENT '订单编码', user_code VARCHAR(255) COMMENT '客户编号', product_name VARCHAR(255) COMMENT '产品名称', sales_province VARCHAR(255) COMMENT '销售省份', sales_number INT COMMENT '销量', create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间');

DROP EVENT IF EXISTS face_sales_data_task1;

CREATE EVENT IF NOT EXISTS face_sales_data_task1 ON SCHEDULE EVERY 1 MINUTE STARTS '2024-01-03 21:17:00' ON COMPLETION PRESERVE ENABLE DO BEGIN /* generate random values */ SET @user_code = FLOOR(RAND()*900000000 + 100000000); SET @order_code = MD5(FLOOR(RAND()*900000000 + 100000000)); SET @product_name = ELT(CEILING(RAND()*8), 'iPhone 15','iPhone 15 Pro','iPhone 15 Pro Max','Xiaomi 14','Xiaomi 14 Pro','Huawei Mate 60','Huawei Mate 60 Pro','Huawei Mate 60 Pro+'); SET @sales_province = ELT(CEILING(RAND()*34), '河北省','山西省',...,'香港特别行政区','澳门特别行政区'); SET @sales_number = FLOOR(RAND()*1000); INSERT INTO sql_test1.face_sales_data (sales_date, order_code, user_code, product_name, sales_province, sales_number) VALUES (CURDATE(), @order_code, @user_code, @product_name, @sales_province, @sales_number); END;

After the event runs, each inserted row’s create_time differs by roughly one minute, confirming successful scheduling.

To stop the data generation, disable the event:

ALTER EVENT face_sales_data_task1 DISABLE;

Another example – scheduled statistics : An event that calls a stored procedure get_table_info1() every 10 minutes for one week.

DROP EVENT IF EXISTS get_table_info1_task1;

CREATE EVENT IF NOT EXISTS get_table_info1_task1 ON SCHEDULE EVERY 10 MINUTE STARTS CURRENT_TIMESTAMP ENDS CURRENT_TIMESTAMP + INTERVAL 1 WEEK ON COMPLETION PRESERVE ENABLE DO CALL get_table_info1();

Disabling the event is done with:

ALTER EVENT get_table_info1_task1 DISABLE;

Summary : Using MySQL events and scheduled tasks automates repetitive database work, improves efficiency, and frees developers to focus on higher‑value problems, while the ability to combine dynamic SQL and stored procedures enables real‑time data processing without manual intervention.