MySQL 定時任務，解放雙手，輕鬆實現自動化

1 概念

Mysql 事件是一種在特定時間點自動執行的數據庫操作，也可以稱呼爲定時任務，它可以自動執行更新數據、插入數據、刪除數據等操作，無需人工干預。

優勢：

自動化： 可以定期執行重複性的任務，無需手動干預。
靈活性： 可以根據需求定製事件，靈活控制任務的執行時間和頻率。
提高效率： 可以在非高峯時段執行耗時任務，減少對數據庫性能的影響。

常見的應用場景有定時備份數據庫，清理和統計數據。

2 常見操作

事件調度器操作

查看事件調度器是否開啓：ON 表示已開啓。

show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

開啓和關閉事件調度器：

# 開啓事件調度器
set global event_scheduler = ON;
# 關閉事件調度器
set global event_scheduler = OFF;

更改配置文件：進入 my.ini 文件修改，重啓 Mysql 服務器，永久生效。

# 事件調度器啓動狀態
event_scheduler = on

查看事件

使用 show 或者 select 語句查看當前數據庫中所有的事件。

show events;
select * from information_schema.events;

創建事件

使用 create event 語句創建一個事件，

基本語法：

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：可選，用於定義事件執行時檢查權限的用戶。
if not exists：可選，一般都加上，用於判斷要創建的事件是否存在。
event_name：定義指定的事件名，是用來唯一標識事件的名稱。在同一個數據庫中，事件名稱必須是唯一的。
on schedule schedule_body：schedule_body 用於定義執行的時間和時間間隔。
on completion [not] preserve：可選，指定事件是否循環執行，默認爲一次執行，即 not preserve。
enable | disable | disable on slave：可選，指定事件的一種屬性，enable 表示啓動，disable 表示關閉或者下線，disable on slave 表示從屬性上禁用，默認啓動
comment ‘comment’：可選，添加事件的註釋。
do event_body：必選，event_body 用於指定事件啓動時所要執行的代碼，可以是任何有效的 sql 語句、存儲過程或者一個計劃執行的事件。如果包含多條語句，可以使用 begin … end 複合結構。

schedule_body 語法：

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

at timestamp：用於一次性活動，指定事件僅在 timestamp 給出的日期和時間執行一次，時間戳必須同時包含日期和時間，或者必須是解析爲日期時間值的表達式，如果日期已過，則會出現警告。

# 相當於“三週兩天後”。此類子句的每個部分必須以+ interval。
at current_timestamp + interval 3 week + interval 2 day

interval 語法：

interval:
    quantity {year | quarter | month | day | hour | minute |
              week | second | year_month | day_hour | day_minute |
              day_second | hour_minute | hour_second | minute_second}

every interval：每隔一段時間執行事件，指定時間區間內每隔多長時間發生一次，interval 其值由一個數值和單位 (quantity) 組成，如 4 week 表示 4 周，’1:10’ HOUR_MINUTE 表示 1 小時 10 分鐘。
starts timestamp：指定事件的開始時間，timestamp 爲時間戳，日期時間值表達式。
ends timestamp：指定事件的結束時間，timestamp 爲時間戳，日期時間值表達式。

常見時間調度：

# 每30分鐘執行一次
on schedule every 30 minute
# 從 2024-01-03 18:00:00 開始，每1小時執行一次
on schedule every 1 hour
  starts '2024-01-03 18:00:00'
# 從現在起30分鐘後開始，四周後結束，這段期間內每12小時執行一次
on schedule every 12 hour 
    starts current_timestamp + interval 30 minute 
    ends current_timestamp + interval 4 week

刪除事件

使用 drop event 語句刪除該事件。

drop event [if exists] event_name;

啓動與關閉事件

使用 alter event 語句對事件進行修改。

# 啓動事件
alter event event_name enable;
# 關閉事件
alter event event_name disable;

3 精選示例

構造實時數據

需求：每分鐘錄入關於產品、省份的訂單銷售數據。

# 表新建
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 '更新時間'
);
# 創建事件任務，多條語句用 begin ... end; 包住。
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);-- 隨機生成銷量
    select @user_code,@order_code,@product_name,@sales_province,@sales_number;-- 查看生成的數據
    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;
select * from sql_test1.face_sales_data;

通過創建事件後，過一段時間再一次查看錶，可以發現每條記錄 create_time 相差 1，說明任務調度設置成功，這樣就可以自動模擬實時銷售數據啦。

如果要停止錄入，可以執行以下代碼關閉事件。

alter event face_sales_data_task1 disable;

定時統計數據

除了能模擬實時數據操作數據表外，也可以執行存儲過程統計數據。

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();
# 下線
alter event get_table_info1_task1 disable;

4 總結

創建事件或定時任務可以解決很多重複性工作，配合着動態 sql 和存儲過程能起到實時更新數據功能，不需要人工干預，提高了工作效率，讓我們有更多的時間學習和處理其它問題。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/IyXebZ1han7HmMMqxtsbTQ