MySQL 觸發器實現 Oracle 風格物化視圖：增量刷新完整代碼

Oracle 物化視圖支援 ON COMMIT FAST REFRESH（提交時增量刷新）。MySQL 無原生物化視圖，但可透過 **觸發器** 在基表 INSERT/UPDATE/DELETE 時同步維護彙總表，實現同等效果。本文提供完整實現代碼。

一、基表與物化視圖表結構

-- 基表：訂單明細
CREATE TABLE Orders (
  order_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(30) NOT NULL,
  price DECIMAL(10,0) NOT NULL,
  amount SMALLINT NOT NULL,
  PRIMARY KEY (order_id)
) ENGINE=InnoDB;

-- 物化視圖表：商品銷售彙總
CREATE TABLE Order_mv (
  product_name VARCHAR(30) NOT NULL,
  price_sum DECIMAL(8,2) NOT NULL,
  amount_sum INT NOT NULL,
  price_avg FLOAT NOT NULL,
  order_cnt INT NOT NULL,
  UNIQUE INDEX (product_name)
) ENGINE=InnoDB;

二、INSERT 觸發器（新增訂單）

DELIMITER $$
CREATE TRIGGER tgr_Orders_insert
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
  DECLARE old_price_sum DECIMAL(8,2) DEFAULT 0;
  DECLARE old_amount_sum INT DEFAULT 0;
  DECLARE old_price_avg FLOAT DEFAULT 0;
  DECLARE old_order_cnt INT DEFAULT 0;

  SELECT IFNULL(price_sum,0), IFNULL(amount_sum,0), IFNULL(price_avg,0), IFNULL(order_cnt,0)
  INTO old_price_sum, old_amount_sum, old_price_avg, old_order_cnt
  FROM Order_mv WHERE product_name = NEW.product_name;

  SET @new_price_sum = old_price_sum + NEW.price;
  SET @new_amount_sum = old_amount_sum + NEW.amount;
  SET @new_order_cnt = old_order_cnt + 1;
  SET @new_price_avg = @new_price_sum / @new_order_cnt;

  REPLACE INTO Order_mv
  VALUES (NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_order_cnt);
END$$
DELIMITER ;

三、UPDATE 觸發器（修改訂單）

DELIMITER $$
CREATE TRIGGER tgr_Orders_update
BEFORE UPDATE ON Orders
FOR EACH ROW
BEGIN
  DECLARE old_price_sum DECIMAL(8,2) DEFAULT 0;
  DECLARE old_amount_sum INT DEFAULT 0;
  DECLARE old_price_avg FLOAT DEFAULT 0;
  DECLARE old_order_cnt INT DEFAULT 0;
  DECLARE cur_price DECIMAL(10,0) DEFAULT 0;
  DECLARE cur_amount SMALLINT DEFAULT 0;

  SELECT price, amount INTO cur_price, cur_amount FROM Orders WHERE order_id = NEW.order_id;

  SELECT IFNULL(price_sum,0), IFNULL(amount_sum,0), IFNULL(price_avg,0), IFNULL(order_cnt,0)
  INTO old_price_sum, old_amount_sum, old_price_avg, old_order_cnt
  FROM Order_mv WHERE product_name = NEW.product_name;

  SET @new_price_sum = old_price_sum - cur_price + NEW.price;
  SET @new_amount_sum = old_amount_sum - cur_amount + NEW.amount;
  SET @new_order_cnt = old_order_cnt;
  SET @new_price_avg = @new_price_sum / @new_order_cnt;

  REPLACE INTO Order_mv
  VALUES (NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_order_cnt);
END$$
DELIMITER ;

四、DELETE 觸發器（刪除訂單）

DELIMITER $$
CREATE TRIGGER tgr_Orders_delete
AFTER DELETE ON Orders
FOR EACH ROW
BEGIN
  DECLARE old_price_sum DECIMAL(8,2) DEFAULT 0;
  DECLARE old_amount_sum INT DEFAULT 0;
  DECLARE old_price_avg FLOAT DEFAULT 0;
  DECLARE old_order_cnt INT DEFAULT 0;
  DECLARE cur_price DECIMAL(10,0) DEFAULT 0;
  DECLARE cur_amount SMALLINT DEFAULT 0;

  SELECT price, amount INTO cur_price, cur_amount FROM Orders WHERE order_id = OLD.order_id;

  SELECT IFNULL(price_sum,0), IFNULL(amount_sum,0), IFNULL(price_avg,0), IFNULL(order_cnt,0)
  INTO old_price_sum, old_amount_sum, old_price_avg, old_order_cnt
  FROM Order_mv WHERE product_name = OLD.product_name;

  SET @new_price_sum = old_price_sum - OLD.price;
  SET @new_amount_sum = old_amount_sum - OLD.amount;
  SET @new_order_cnt = old_order_cnt - 1;

  IF @new_order_cnt > 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 ;

五、已知缺陷與改進方向

• DELETE 觸發器 Bug：當某商品最後一筆訂單被刪除時，SELECT ... FROM Orders WHERE order_id = OLD.order_id 返回空，導致變數為 0，彙總邏輯失效。需改用 OLD.price/OLD.amount 直接取值（MySQL 觸發器中 OLD 列可直接引用）。
• 併發安全：高併發下多事務同時修改同一商品彙總行，會產生鎖等待甚至死鎖。建議：
  • 使用 GET_LOCK() 應用層鎖序列化
  • 或改為異步刷新（定時任務/消息佇列）
• 全量重建：定期執行全量重算校準漂移：

REPLACE INTO Order_mv
SELECT product_name, SUM(price), SUM(amount), AVG(price), COUNT(*)
FROM Orders GROUP BY product_name;

六、適用場景評估