MySQL Event 定時任務實戰：創建存儲過程 + 排程事件每 30 秒自動更新

1. 建庫建表

CREATE DATABASE test1;
USE test1;
CREATE TABLE examinfo (id INT AUTO_INCREMENT PRIMARY KEY, endtime DATETIME);
INSERT INTO examinfo VALUES (1, '2011-04-23 23:26:50');

2. 創建存儲過程

CREATE PROCEDURE test()
BEGIN
  UPDATE examinfo SET endtime = NOW() WHERE id = 1;
END;

3. 創建 Event

CREATE EVENT IF NOT EXISTS e_test
ON SCHEDULE EVERY 30 SECOND
ON COMPLETION PRESERVE
DO CALL test();

4. 啟用/控制事件調度器

-- 查看狀態
SHOW VARIABLES LIKE '%sche%';
-- 啟用
SET GLOBAL event_scheduler = 1;
-- 禁用事件
ALTER EVENT e_test ON COMPLETION PRESERVE DISABLE;
-- 啟用事件
ALTER EVENT e_test ON COMPLETION PRESERVE ENABLE;

5. 驗證

等待片刻查詢 examinfo 表，endtime 會每 30 秒自動刷新為當前時間。