觸發器 是 MySQL 響應 INSERT/UPDATE/DELETE 事件自動執行預定義 SQL 的機制,常用於審計日誌、數據同步、派生列維護、業務規則強制執行。本文深度解析觸發器語法、同步數據實戰模式、動態生成觸發器代碼工具 (含原文 PHP 工具改進版)、關鍵陷阱 (遞歸、事務、性能、複製) 及現代替代方案 (CDC、Binlog、雙寫、物化視圖)。
CREATE TRIGGER trigger_name
trigger_time trigger_event ON table_name
FOR EACH ROW
BEGIN
-- trigger body
END;
BEFORE (修改 NEW 值、驗證) / AFTER (日誌、同步、級聯操作)。INSERT / UPDATE / DELETE。INSERT 只有 NEW;DELETE 只有 OLD;UPDATE 兩者皆有。-- 同步 INSERT
CREATE TRIGGER src_after_ins AFTER INSERT ON src_table
FOR EACH ROW
BEGIN
INSERT INTO dst_table (col1, col2, ...) VALUES (NEW.col1, NEW.col2, ...);
END;
-- 同步 UPDATE
CREATE TRIGGER src_after_upd AFTER UPDATE ON src_table
FOR EACH ROW
BEGIN
UPDATE dst_table
SET col1 = NEW.col1, col2 = NEW.col2, ...
WHERE pk = OLD.pk; -- 用主鍵定位
END;
-- 同步 DELETE
CREATE TRIGGER src_after_del AFTER DELETE ON src_table
FOR EACH ROW
BEGIN
DELETE FROM dst_table WHERE pk = OLD.pk;
END;
雙向同步時,A 觸發同步到 B,B 觸發再同步回 A → 無限循環。解決方案:
sync_flag 列或用戶變量 @sync_active。CREATE TRIGGER a_to_b AFTER INSERT ON a
FOR EACH ROW
BEGIN
IF @sync_active IS NULL OR @sync_active = 0 THEN
SET @sync_active = 1;
INSERT INTO b (...) VALUES (NEW...);
SET @sync_active = 0;
END IF;
END;
注意:用戶變量 會話級別,複製環境下從庫 SQL 線程會話不同,**雙向同步極其危險,生產環境強烈不推薦**,改用 CDC。
原文提供的 PHP 工具基於 mysql_* 擴展 (已棄用/移除),且存在 SQL 注入、邏輯缺陷。下面提供 PDO + Prepared Statements + 現代化輸出 的重構版:
<?php
/**
* MySQL 觸發器同步代碼生成器 (PDO 版)
* 用法: php gen_trigger.php --host=localhost --db=test --src=source_table --dst=target_table --pk=id
*/
$options = getopt("", ["host:", "db:", "user:", "pass:", "src:", "dst:", "pk:"]);
$host = $options['host'] ?? 'localhost';
$db = $options['db'] ?? '';
$user = $options['user'] ?? 'root';
$pass = $options['pass'] ?? '';
$src = $options['src'] ?? '';
$dst = $options['dst'] ?? '';
$pk = $options['pk'] ?? 'id';
if (!$db || !$src || !$dst) {
fwrite(STDERR, "Usage: --db=DB --src=SRC_TABLE --dst=DST_TABLE [--pk=PK_COL]\n");
exit(1);
}
try {
$pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
} catch (PDOException $e) {
fwrite(STDERR, "Connect failed: " . $e->getMessage() . "\n");
exit(1);
}
// 獲取列信息
$stmt = $pdo->query("SHOW COLUMNS FROM `$src`");
$cols = $stmt->fetchAll(PDO::FETCH_COLUMN, 0); // Field 名
$pkExists = in_array($pk, $cols);
if (!$pkExists) {
fwrite(STDERR, "PK column '$pk' not found in $src\n");
exit(1);
}
// 過濾非同步列 (如自增ID、時間戳默認值等,視業務而定)
$syncCols = array_filter($cols, fn($c) => $c !== $pk); // 示例:排除PK,實際按需調整
$esc = fn($v) => "`" . str_replace("`", "``", $v) . "`";
$colList = implode(", ", array_map($esc, $syncCols));
$newVals = implode(", ", array_map(fn($c) => "NEW." . $esc($c), $syncCols));
$oldPk = "OLD." . $esc($pk);
$newPk = "NEW." . $esc($pk);
$triggerBase = $src . "_sync";
// 輸出 SQL
echo "-- Generated at " . date('Y-m-d H:i:s') . "\n";
echo "DELIMITER $$\n\n";
// 1. AFTER INSERT
echo "CREATE TRIGGER {$triggerBase}_ins AFTER INSERT ON `$src`\n";
echo "FOR EACH ROW\nBEGIN\n";
echo " INSERT INTO `$dst` ($colList) VALUES ($newVals);\n";
echo "END$$\n\n";
// 2. AFTER UPDATE
echo "CREATE TRIGGER {$triggerBase}_upd AFTER UPDATE ON `$src`\n";
echo "FOR EACH ROW\nBEGIN\n";
echo " UPDATE `$dst` SET\n";
$updLines = array_map(fn($c) => " " . $esc($c) . " = NEW." . $esc($c), $syncCols);
echo implode(",\n", $updLines) . "\n";
echo " WHERE $esc($pk) = $oldPk;\n";
echo "END$$\n\n";
// 3. AFTER DELETE
echo "CREATE TRIGGER {$triggerBase}_del AFTER DELETE ON `$src`\n";
echo "FOR EACH ROW\nBEGIN\n";
echo " DELETE FROM `$dst` WHERE $esc($pk) = $oldPk;\n";
echo "END$$\n\n";
echo "DELIMITER ;\n";
?>
php gen_trigger.php --db=shop --src=orders --dst=orders_archive --pk=order_id
| 陷阱 | 後果 | 規避策略 |
|---|---|---|
| 遞歸觸發 | 死循環、棧溢出、數據膨脹 | 用戶變量標誌位、應用層標誌、避免雙向同步 |
| 事務耦合 | 觸發器失敗導致主操作回滾、長事務、鎖競爭 | 核心業務異步化 (MQ/CDC)、觸發器內只做極簡操作 |
| 性能放大器 | 批量操作 (如 UPDATE 10萬行) 觸發 10 萬次觸發器執行 | 禁用觸發器執行批量操作、改用存儲過程/應用層批量同步 |
| 複製延遲/不一致 | 主從延遲、從庫觸發器重複執行、行格式差異 | 從庫禁用觸發器 (replica_skip_triggers)、用 CDC 替代 |
| Schema 變更同步 | 源表加列、改類型,目標表不同步導致報錯 | DDL 變更流程強制同步修改觸發器、或用 CDC 自適應 |
| 調試困難 | 觸發器內部錯誤難定位、無堆疊跟蹤 | 關鍵邏輯移至存儲過程/應用層、詳細日誌記錄 |
| 外鍵約束衝突 | 同步順序導致 FK 錯誤 | 按拓撲順序同步、暫時禁用 FK 檢查 |
outbox 表保證一致性。觸發器曾是數據庫層同步的「瑞士軍刀」,但在微服務、雲原生、高併發、高可用的現代架構下,其同步阻塞主事務、難以調試、Schema 耦合、複製風險等缺陷被無限放大。CDC 基於 Binlog 的異步流式架構才是當前與未來的標準答案。保留觸發器僅限於:極簡單的審計日誌、單表派生列維護、無法改造的存量邊緣場景。對於數據同步,請擁抱流式架構,讓數據庫專注於存儲與索引,將流轉邏輯還給中間件與應用層。