Automate MySQL Tasks with Events: Hands‑Free Scheduling

1. Concept

MySQL events are scheduled tasks that execute automatically at a specific time or interval, allowing operations such as INSERT, UPDATE, or DELETE without manual intervention.

2. Advantages

Automation: Repetitive jobs run without human action.

Flexibility: Execution time and frequency can be customized.

Efficiency: Heavy‑weight tasks can be run during off‑peak periods, reducing impact on database performance.

Typical scenarios include periodic database backups and data cleanup or aggregation.

3. Common Operations

Check whether the event scheduler is enabled: show variables like '%event_scheduler%'; Enable or disable the scheduler globally:

# Enable the scheduler
set global event_scheduler = ON;
# Disable the scheduler
set global event_scheduler = OFF;

Persist the setting by editing my.ini and restarting MySQL: event_scheduler = on View existing events:

show events;
select * from information_schema.events;

4. Creating an Event

Basic syntax (all optional clauses are described):

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;

definer

: optional user whose privileges are checked when the event runs. if not exists: prevents error if the event already exists. event_name: unique identifier within the database. on schedule schedule_body: defines execution time and interval. on completion [not] preserve: determines whether the event repeats (preserve) or runs once (not preserve). enable | disable | disable on slave: controls the event’s active state. comment: optional description. do event_body: the SQL statements to execute; multiple statements can be wrapped in BEGIN … END.

5. Schedule Body Syntax

at timestamp [+ interval interval] ...
| every interval
    [starts timestamp [+ interval interval ...]]
    [ends timestamp [+ interval interval ...]]

at timestamp

runs once at the given date‑time. every interval repeats at the specified interval (e.g., 4 week or '1:10' HOUR_MINUTE). starts and ends optionally bound the schedule.

6. Example: Construct Real‑Time Sales Data

Requirement: insert a new sales record every minute.

# Drop and create the target table
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 the event that runs every minute
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
    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;

# Verify the generated rows
SELECT * FROM sql_test1.face_sales_data;

After the event runs for a while, the create_time column shows a one‑minute difference between rows, confirming successful scheduling.

To stop data generation, disable the event:

ALTER EVENT face_sales_data_task1 DISABLE;

7. Example: Periodic Statistics

An event can also call a stored procedure on a regular basis:

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();
# Disable after use
ALTER EVENT get_table_info1_task1 DISABLE;

8. Deleting and Controlling Events

Remove an event: DROP EVENT IF EXISTS event_name; Enable or disable an existing event:

ALTER EVENT event_name ENABLE;
ALTER EVENT event_name DISABLE;

9. Summary

Creating MySQL events or scheduled tasks automates many repetitive operations; combined with dynamic SQL and stored procedures they provide real‑time data updates without manual effort, improving productivity and freeing time for other tasks.