MySQL Event Scheduler: Concepts, Operations, and Practical Examples

MySQL events are scheduled tasks that automatically execute SQL statements at specified times, enabling automation, flexibility, and efficiency for operations like periodic backups or data cleanup.

Advantages : automation of repetitive tasks, flexible timing control, and reduced impact on database performance during off‑peak hours.

Event Scheduler Operations :

show variables like '%event_scheduler%';
# Enable event scheduler
set global event_scheduler = ON;
# Disable event scheduler
set global event_scheduler = OFF;

Configuration can be persisted by editing my.ini and restarting MySQL.

Viewing Events :

show events;
select * from information_schema.events;

Creating an Event uses the syntax:

create event [if not exists] event_name
on schedule schedule_body
[on completion [not] preserve]
[enable | disable]
[comment 'comment']
do event_body;

Schedule Body Syntax supports one‑time execution ( at timestamp ) and recurring execution ( every interval ) with optional starts and ends clauses.

Examples of common schedules:

# Every 30 minutes
on schedule every 30 minute;
# Every hour starting at a specific time
on schedule every 1 hour starts '2024-01-03 18:00:00';
# Every 12 hours, starting 30 minutes from now, ending after 4 weeks
on schedule every 12 hour starts current_timestamp + interval 30 minute ends current_timestamp + interval 4 week;

Deleting an Event :

drop event [if exists] event_name;

Enabling/Disabling an Event :

alter event event_name enable;
alter event event_name disable;

Practical Example – Real‑Time Sales Data : an event runs every minute to generate random sales records and insert them into a face_sales_data table, demonstrating automated data simulation.

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 '更新时间'
);
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
    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;

Another example shows an event that calls a stored procedure every ten minutes for periodic data aggregation.

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();

In summary, MySQL events enable automated, repeatable database tasks, reducing manual effort and improving efficiency.