MySQL 觸發器三種狀態實戰：INSERT 同步、UPDATE 同步、DELETE 同步、複雜 INSERT 字段映射

場景：user1 變更同步至 user2

1. INSERT 同步（取最新一筆）

CREATE TRIGGER `user1_user2` AFTER INSERT ON `user1`
FOR EACH ROW
INSERT INTO user2
SELECT * FROM user1 ORDER BY id DESC LIMIT 1
WHERE id >= LAST_INSERT_ID() AND id <= LAST_INSERT_ID() + ROW_COUNT();

2. UPDATE 同步（映射欄位）

CREATE TRIGGER `update_user` AFTER UPDATE ON `user1`
FOR EACH ROW
UPDATE user2 SET name = NEW.user, info = NEW.pwd WHERE id = OLD.id;

3. DELETE 同步

CREATE TRIGGER `del_user` AFTER DELETE ON `user1`
FOR EACH ROW
DELETE FROM user2 WHERE id = OLD.id;

4. 複雜 INSERT：字段映射、函數轉換、關聯查詢

CREATE TRIGGER `insert_user2` AFTER INSERT ON `user1`
FOR EACH ROW
INSERT INTO user2
SET `id` = NEW.id,
    `name` = NEW.user,
    `info` = NEW.pwd,
    `date2` = UNIX_TIMESTAMP(NEW.date1),
    `enum2` = NEW.enum1 - 1,
    `aid` = (SELECT aid FROM protype WHERE id = NEW.type_id);

關鍵技巧解析

• LAST_INSERT_ID() + ROW_COUNT() 精確定位批次插入的新行範圍。
• SET col = 值 語法比 VALUES(...) 更適合複雜映射、函數運算、子查詢。
• UNIX_TIMESTAMP()、算術運算、子查詢均可直接在 SET 子句使用。
• 同步邏輯集中在觸發器，應用層無感，保證強一致性。

避坑提醒

• 觸發器內不可有事務控制，若主表操作回滾，觸發器動作也會回滾（隱含在同一交易）。
• 大量批次寫入時，觸發器逐行執行會顯著降低寫入效能；可考慮應用層批次同步或異步隊列替代。
• 同步目標表 user2 需有相應索引（如 id）否則 UPDATE/DELETE 會全表掃描。