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

MySQL 觸發器完整指南：創建、查看、刪除與實戰應用模式

編輯：MySQL 綜合教學

MySQL Trigger 核心語法、執行時機、權限模型與最佳實踐

觸發器是針對表級 DML 事件（INSERT/UPDATE/DELETE）自動執行的存儲程序，支援行級觸發，廣泛用於審計日誌、衍生列維護、跨表同步、業務規則強制執行。本文系統梳理創建語法（單/多語句）、查看元數據、刪除操作、NEW/OLD 伪表、權限要求、效能影響與替代方案。

一、核心概念與限制

支援版本：MySQL 5.0.2+
觸發事件：INSERT、UPDATE、DELETE（不支援 TRUNCATE、DDL、SELECT）
觸發時機：BEFORE（寫入前，可修改 NEW）、AFTER（寫入後，不可修改 NEW）
粒度：FOR EACH ROW（行級），不支援 STATEMENT 級
最多定義：每表每事件每時機最多 1 個（5.7.2+ 支援多個，FOLLOWS/PRECEDES 排序）
權限：需 TRIGGER 權限（5.1.6+），SUPER 用於變更 Binlog 格式相關

二、創建語法詳解

1. 單語句觸發器

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    statement_1;
    statement_2;
    ...
END;

三、實戰範例：審計日誌自動記錄

1. 建表

DELIMITER //
CREATE TRIGGER save_time_trigger
BEFORE INSERT ON student_info
FOR EACH ROW
    INSERT INTO time_log(save_time, save_info) VALUES (NOW(), 'INSERT');
//
DELIMITER ;

3. BEFORE INSERT 多語句觸發器（記錄操作類型與新值）

CREATE TRIGGER audit_student_update
BEFORE UPDATE ON student_info
FOR EACH ROW
BEGIN
    IF OLD.name != NEW.name OR OLD.age != NEW.age THEN
        INSERT INTO time_log(save_time, save_info) 
        VALUES (NOW(), CONCAT('UPDATE: ', OLD.name, '→', NEW.name, ', age: ', OLD.age, '→', NEW.age));
    END IF;
END;

5. DELETE 觸發器：軟刪除/歸檔

事件NEWOLD可修改 NEW
INSERT新行值不可用BEFORE 可修改
UPDATE新值舊值BEFORE 可修改
DELETE不可用被刪行值不可用


五、查看與管理觸發器
1. 列出所有觸發器
SELECT * FROM information_schema.triggers 
WHERE TRIGGER_NAME = 'trigger_name' AND TRIGGER_SCHEMA = 'db_name'\G

3. 刪除觸發器
維度影響優化建議
執行開銷同步阻塞 DML，增加延遲邏輯極簡化、避免複雜查詢、必要時異步化(消息隊列)
鎖持有時間延長行鎖/表鎖持有觸發器內不做大事務、不鎖其他表
Binlog 體積觸發器內 DML 同步記錄評估 Binlog 增長、備份窗口影響
遞歸觸發觸發器修改同表 → 死循環嚴禁觸發器操作觸發源表，使用標誌位/標誌表判斷
調試難度隱式執行，難以追蹤完整日誌、單元測試覆蓋、變更審查強制項


七、常見反模式與替代方案

跨表同步：觸發器 → 改用 CDC (Debezium/Canal) + 消息隊列 + 消費者更新
複雜業務規則：觸發器 → 應用層 Service 驗證、資料庫 Constraint (CHECK/外鍵)
衍生列計算：觸發器 → Generated Columns (MySQL 5.7+ VIRTUAL/STORED)
審計日誌：觸發器 → Binlog 解析 (Canal/Maxwell) 寫入獨立審計庫


八、總結
觸發器是資料庫層面的「切面編程」，對於簡單、確定性、低頻、強一致性要求高的行級副作用（如審計、軟刪除、衍生列維護）是優雅方案。但對於複雜邏輯、高併發寫入、跨表/跨庫操作、需異步解耦的場景，應用層事件驅動 + CDC + 消息隊列是更現代、可擴展的架構選擇。建議：嚴格控制觸發器數量，建立觸發器變更審查機制，定期評估效能影響並遷移至應用層。