MySQL 觸發器實現 Oracle 物化視圖：增量維護聚合表完整實戰

背景與目標

Oracle 物化視圖支持 ON COMMIT FAST REFRESH，數據實時增量維護。MySQL 無原生物化視圖，但可用 **觸發器 + 聚合表** 實現同等效果。

場景：訂單表 Orders → 產品維度聚合表 Order_mv（價格求和、數量求和、平均價、訂單數）。

一、基礎表結構

二、INSERT 觸發器：新增訂單增量更新

三、UPDATE 觸發器：訂單修改差值更新

四、DELETE 觸發器：刪除訂單減量更新

 0 THEN
        SET @new_price_avg = @new_price_sum / @new_order_cnt;
        REPLACE INTO Order_mv
        VALUES (OLD.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_order_cnt);
    ELSE
        -- 最後一筆刪除，移除聚合行
        DELETE FROM Order_mv WHERE product_name = OLD.product_name;
    END IF;
END$$
DELIMITER ;

五、已知 Bug 與修正方案

Bug：最後一筆刪除時 OLD.product_name 變量作用域問題

原文中 DELETE FROM Order_mv WHERE product_name = @old.name 變量名錯誤，應為 OLD.product_name。

修正：

六、全量重建腳本（定期校正/初始化）

七、性能與一致性考量

八、避坑清單

1. ✅ 使用 REPLACE INTO 或 INSERT ... ON DUPLICATE KEY UPDATE 保冪等
2. ✅ IFNULL() 防御首次插入時聚合表無記錄
3. ✅ UPDATE 觸發器用 BEFORE 讀取舊值，避免死鎖
4. ✅ 變量名嚴格區分 NEW./OLD. 與用戶變量 @var
5. ✅ 定期對比 Orders 聚合與 Order_mv 差異，發現偏差觸發告警

總結

三個觸發器約 80 行代碼，即可在 MySQL 實現 Oracle 級的 FAST REFRESH ON COMMIT 物化視圖效果。關鍵在於：**增量計算邏輯正確、變量作用域清晰、邊界條件（歸零刪除）覆蓋完整**。生產環境建議配合定時全量校驗任務，雙重保障數據準確。