您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 定時任務與觸發器實戰：Event Scheduler 週期執行、存儲過程封裝、動態 SQL 報表生成

編輯：MySQL 綜合教學

MySQL 內建 Event Scheduler 替代外部 Cron，實現庫級定時任務。本文覆蓋 Event 啟用/關閉、週期執行、存儲過程封裝、觸發器條件異步寫入、動態 SQL 月/周報表生成全實戰。

一、Event Scheduler 基礎操作


-- 查看狀態
SHOW VARIABLES LIKE '%sche%';  -- event_scheduler = ON/OFF

-- 啟用/關閉
SET GLOBAL event_scheduler = ON;   -- 或 OFF
-- 永久生效需寫入 my.cnf: event_scheduler=ON

-- 任務控制
ALTER EVENT e_test ON COMPLETION PRESERVE DISABLE;  -- 暫停
ALTER EVENT e_test ON COMPLETION PRESERVE ENABLE;   -- 恢復

二、基礎定時任務實例

1. 建表 + 存儲過程


CREATE TABLE examinfo (
    id INT PRIMARY KEY,
    endtime DATETIME
);

CREATE PROCEDURE update_examinfo()
BEGIN
    UPDATE examinfo SET endtime = NOW() WHERE id = 14;
END;

2. 創建 Event：每 30 秒執行一次


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

3. 簡單記錄當前時間


CREATE TABLE aa (ts DATETIME);
CREATE EVENT IF NOT EXISTS e_log_time
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO aa VALUES (NOW());

三、觸發器：條件異步寫入緩存表


DELIMITER //
CREATE TRIGGER trigger_htmlcache BEFORE INSERT ON t_model
FOR EACH ROW BEGIN
    IF CURDATE() < NEW.time THEN
        INSERT INTO t_htmlcache (id, url) VALUES (NEW.id, NEW.url);
    END IF;
END;
//
DELIMITER ;

通過 INSERT INTO t_model ... 觸發驗證。

四、動態 SQL 報表存儲過程：月報/周報自動生成


DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ireport` $$
CREATE PROCEDURE `sp_ireport`(
    IN queryType VARCHAR(20),   -- 'insert'/'month'/'week'
    IN daytime VARCHAR(20),     -- 日期參數
    IN p_ids VARCHAR(50),       -- 省份 ID 列表
    IN c_ids VARCHAR(50),       -- 城市 ID 列表
    IN ct1_ids VARCHAR(50),     -- 一級分類 ID
    IN ct2_ids VARCHAR(50),     -- 二級分類 ID
    IN ku VARCHAR(50),          -- 库名（用於臨時表 schema）
    IN ireport_chart VARCHAR(50) -- 'report'/'chart'
)
BEGIN
    DECLARE i INT DEFAULT 1;

    -- 1. 每日增量入庫
    IF queryType IN ('insert','INSERT') THEN
        INSERT INTO ireport (pid, cid, ct1id, ct2id, creatTime, crawlerNumber, WEEK)
        SELECT province, cityid, category1id, category2id,
               CURRENT_DATE, COUNT(*),
               FLOOR(DAYOFMONTH(CURRENT_DATE)/8)+1
        FROM t_model
        WHERE time > CURRENT_DATE-1 AND time < CURRENT_DATE
          AND province IS NOT NULL AND cityid IS NOT NULL
        GROUP BY province, cityid, category1id, category2id;
    END IF;

    -- 2. 月報生成
    IF queryType IN ('month','MONTH') THEN
        -- 動態建臨時表
        IF EXISTS (SELECT 1 FROM information_schema.TABLES WHERE TABLE_NAME='tmp_result' AND TABLE_SCHEMA=ku) THEN
            DROP TABLE tmp_result;
        END IF;
        CREATE TABLE tmp_result (
            pid VARCHAR(50), pName VARCHAR(50), cid VARCHAR(50), cName VARCHAR(50),
            ct1id VARCHAR(50), ct1Name VARCHAR(50), ct2id VARCHAR(50), ct2Name VARCHAR(50),
            month1 INT, month2 INT, ..., month12 INT, heji INT
        );

        -- 動態 SQL 註冊維度
        SET @SqlCmd = 'INSERT INTO tmp_result (pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name)
            SELECT pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name FROM (
                SELECT ia.pid, a.name pname, ia.cid, b.name cname, ia.ct1id, c.name ct1name, ia.ct2id, d.name ct2name
                FROM ireport ia
                LEFT JOIN province a ON ia.pid=a.id
                LEFT JOIN city b ON ia.cid=b.id
                LEFT JOIN t_category1 c ON ia.ct1id=c.id
                LEFT JOIN t_category2 d ON ia.ct2id=d.id
                WHERE YEAR(ia.creatTime)=YEAR(?)
            ) AS ir GROUP BY pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name';
        -- 動態拼接 IN 列表（省略重複邏輯）
        PREPARE stmt1 FROM @SqlCmd;
        SET @a = daytime;
        EXECUTE stmt1 USING @a;
        DEALLOCATE PREPARE stmt1;

        -- 迴圈填充 12 月數據
        WHILE i <= 12 DO
            SET @SqlCmd = CONCAT('UPDATE tmp_result a JOIN (
                SELECT pid,cid,ct1id,ct2id,SUM(crawlerNumber) cn FROM ireport
                WHERE MONTH(creatTime)=', i, ' AND YEAR(creatTime)=YEAR(?)
                GROUP BY pid,cid,ct1id,ct2id
            ) b SET a.month', i, '=b.cn WHERE a.pid=b.pid AND a.cid=b.cid AND a.ct1id=b.ct1id AND a.ct2id=b.ct2id');
            PREPARE stmt1 FROM @SqlCmd;
            SET @a = i; SET @b = daytime;
            EXECUTE stmt1 USING @a,@b;
            DEALLOCATE PREPARE stmt1;
            SET i = i + 1;
        END WHILE;

        -- NULL 補 0、計算合計、輸出報表/圖表數據
        -- （邏輯同理，略）
    END IF;

    -- 3. 周報生成（邏輯同月報，按 WEEK 字段分組）
    IF queryType IN ('week','WEEK') THEN
        -- 類似月報，臨時表字段 week1..week4
        -- 動態 SQL 填充、補 0、合計、輸出
    END IF;
END $$
DELIMITER ;