MySQL 進階管理實戰：觸發器、預存程序與完整性約束

當資料庫規模逐漸擴大，單純的 CRUD 操作已無法滿足複雜的業務需求。企業級應用程式需要自動化的資料處理機制、嚴格的資料完整性保障，以及靈活的權限控制策略。MySQL 提供了一系列進階功能來應對這些挑戰，包括觸發器用於自動執行特定操作、預存程序用於封裝複雜的業務邏輯、外鍵約束用於維護資料的參照完整性，以及檢視用於簡化複雜查詢並強化資料安全。然而，這些強大的功能如果使用不當，可能會帶來效能問題或是維護上的困難。本文將從實務角度出發，探討如何正確運用這些進階功能，避免常見的陷阱，並建立起穩健可靠的資料庫架構。

觸發器的自動化機制

觸發器是一種特殊的預存程序，會在特定的資料庫事件發生時自動執行。這些事件包括 INSERT、UPDATE 與 DELETE 操作，觸發器可以設定在事件發生前或發生後執行。觸發器最常見的應用場景是維護資料的一致性、記錄資料變更的稽核日誌，以及自動計算衍生欄位的值。例如在電子商務系統中，當訂單明細被新增或修改時，可以透過觸發器自動更新訂單的總金額。

在設計觸發器時需要特別注意效能影響，因為觸發器會在每次觸發事件時執行，如果觸發器中包含複雜的查詢或是大量的資料處理，可能會嚴重影響資料庫的整體效能。此外，觸發器的邏輯應該保持簡單明確，避免在觸發器中執行會產生副作用的操作，例如發送電子郵件或是呼叫外部 API，這些操作應該由應用程式層處理。

-- 建立訂單總金額自動計算的觸發器
-- 當訂單明細新增或更新時，自動重新計算訂單總金額

-- 首先建立訂單主檔資料表
CREATE TABLE orders (
  -- order_id: 訂單編號，主鍵
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  -- customer_id: 客戶編號
  customer_id INT NOT NULL,
  -- order_date: 訂單日期
  order_date DATE NOT NULL,
  -- total_amount: 訂單總金額
  -- 這個欄位會由觸發器自動計算與更新
  total_amount DECIMAL(10, 2) DEFAULT 0.00,
  -- status: 訂單狀態（pending, confirmed, shipped, completed）
  status VARCHAR(20) DEFAULT 'pending'
) ENGINE=InnoDB;

-- 建立訂單明細資料表
CREATE TABLE order_items (
  -- item_id: 明細編號，主鍵
  item_id INT AUTO_INCREMENT PRIMARY KEY,
  -- order_id: 關聯的訂單編號，外鍵
  order_id INT NOT NULL,
  -- product_id: 產品編號
  product_id INT NOT NULL,
  -- quantity: 數量
  quantity INT NOT NULL,
  -- unit_price: 單價
  unit_price DECIMAL(10, 2) NOT NULL,
  -- 建立外鍵約束
  -- 確保明細必須關聯到存在的訂單
  FOREIGN KEY (order_id) REFERENCES orders(order_id) 
    ON DELETE CASCADE,
  -- 建立索引加速查詢
  INDEX idx_order (order_id)
) ENGINE=InnoDB;

-- 建立稽核日誌資料表
-- 記錄訂單總金額的變更歷史
CREATE TABLE order_audit_log (
  log_id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  old_amount DECIMAL(10, 2),
  new_amount DECIMAL(10, 2),
  change_type VARCHAR(20),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 變更分隔符號，因為觸發器定義中包含分號
DELIMITER //

-- 建立新增明細後的觸發器
-- AFTER INSERT 表示在資料插入後執行
CREATE TRIGGER after_order_item_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
  -- 宣告變數儲存舊的總金額
  DECLARE old_total DECIMAL(10, 2);
  
  -- 取得訂單當前的總金額
  SELECT total_amount INTO old_total
  FROM orders
  WHERE order_id = NEW.order_id;
  
  -- 更新訂單總金額
  -- 重新計算該訂單所有明細的金額總和
  UPDATE orders
  SET total_amount = (
    SELECT COALESCE(SUM(quantity * unit_price), 0)
    FROM order_items
    WHERE order_id = NEW.order_id
  )
  WHERE order_id = NEW.order_id;
  
  -- 記錄變更到稽核日誌
  -- NEW 關鍵字代表新插入的記錄
  INSERT INTO order_audit_log (order_id, old_amount, new_amount, change_type)
  VALUES (
    NEW.order_id,
    old_total,
    (SELECT total_amount FROM orders WHERE order_id = NEW.order_id),
    'INSERT'
  );
END//

-- 建立更新明細後的觸發器
CREATE TRIGGER after_order_item_update
AFTER UPDATE ON order_items
FOR EACH ROW
BEGIN
  DECLARE old_total DECIMAL(10, 2);
  
  -- 取得訂單當前的總金額
  SELECT total_amount INTO old_total
  FROM orders
  WHERE order_id = NEW.order_id;
  
  -- 重新計算訂單總金額
  UPDATE orders
  SET total_amount = (
    SELECT COALESCE(SUM(quantity * unit_price), 0)
    FROM order_items
    WHERE order_id = NEW.order_id
  )
  WHERE order_id = NEW.order_id;
  
  -- 記錄變更
  INSERT INTO order_audit_log (order_id, old_amount, new_amount, change_type)
  VALUES (
    NEW.order_id,
    old_total,
    (SELECT total_amount FROM orders WHERE order_id = NEW.order_id),
    'UPDATE'
  );
END//

-- 建立刪除明細後的觸發器
CREATE TRIGGER after_order_item_delete
AFTER DELETE ON order_items
FOR EACH ROW
BEGIN
  DECLARE old_total DECIMAL(10, 2);
  
  -- OLD 關鍵字代表被刪除的記錄
  SELECT total_amount INTO old_total
  FROM orders
  WHERE order_id = OLD.order_id;
  
  -- 重新計算訂單總金額
  UPDATE orders
  SET total_amount = (
    SELECT COALESCE(SUM(quantity * unit_price), 0)
    FROM order_items
    WHERE order_id = OLD.order_id
  )
  WHERE order_id = OLD.order_id;
  
  -- 記錄變更
  INSERT INTO order_audit_log (order_id, old_amount, new_amount, change_type)
  VALUES (
    OLD.order_id,
    old_total,
    (SELECT total_amount FROM orders WHERE order_id = OLD.order_id),
    'DELETE'
  );
END//

-- 恢復預設分隔符號
DELIMITER ;

觸發器建立完成後，當對 order_items 資料表進行任何新增、修改或刪除操作時，對應的觸發器就會自動執行，更新訂單的總金額並記錄變更歷史。這種自動化機制確保了資料的一致性，應用程式不需要在每次操作明細時都手動更新訂單總額。

@startuml
!define PLANTUML_FORMAT svg
!theme _none_

skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 14
skinparam minClassWidth 100

|應用程式|
start

:執行訂單明細操作;
note right
  INSERT / UPDATE / DELETE
  order_items 資料表
end note

|MySQL 伺服器|
:觸發對應的觸發器;

if (操作類型?) then (INSERT)
  :執行 after_order_item_insert;
elseif (UPDATE)
  :執行 after_order_item_update;
else (DELETE)
  :執行 after_order_item_delete;
endif

:讀取當前訂單總金額;

:計算新的訂單總金額;
note right
  SUM(quantity * unit_price)
  from order_items
end note

:更新 orders.total_amount;

:記錄稽核日誌;
note right
  儲存到 order_audit_log
  包含舊值、新值與變更類型
end note

:完成觸發器執行;

|應用程式|
:接收操作完成回應;

stop

@enduml

預存程序的設計模式

預存程序是預先編譯並儲存在資料庫中的 SQL 語句集合，可以接受參數、執行複雜的邏輯運算，並回傳結果。相較於在應用程式中執行多個獨立的 SQL 查詢，使用預存程序可以減少網路往返次數，提升效能。預存程序也有助於集中管理業務邏輯，當業務規則需要調整時，只需要修改預存程序而不用變更應用程式代碼。

預存程序中可以使用變數、條件判斷、迴圈等程式控制結構，讓複雜的業務邏輯能夠在資料庫層實作。然而，過度依賴預存程序可能會導致業務邏輯分散在應用程式與資料庫之間，增加維護的複雜度。一般的建議是將資料密集型的操作放在預存程序中，而將業務流程控制保留在應用程式層。

-- 建立訂單處理的預存程序
-- 展示參數使用、錯誤處理與交易控制

DELIMITER //

-- 建立新訂單的預存程序
-- 接受客戶編號與訂單明細，回傳新訂單編號
CREATE PROCEDURE create_order(
  -- IN 參數：輸入參數
  IN p_customer_id INT,
  -- OUT 參數：輸出參數，用於回傳結果
  OUT p_order_id INT
)
BEGIN
  -- 宣告變數用於錯誤處理
  DECLARE exit handler for sqlexception
  BEGIN
    -- 發生錯誤時回復交易
    ROLLBACK;
    -- 設定錯誤訊息
    SET p_order_id = -1;
  END;
  
  -- 開始交易
  -- 確保所有操作要麼全部成功，要麼全部失敗
  START TRANSACTION;
  
  -- 插入訂單主檔
  INSERT INTO orders (customer_id, order_date, status)
  VALUES (p_customer_id, CURDATE(), 'pending');
  
  -- 取得剛插入的訂單編號
  -- LAST_INSERT_ID() 回傳最後一次自動遞增的 ID
  SET p_order_id = LAST_INSERT_ID();
  
  -- 提交交易
  COMMIT;
END//

-- 建立新增訂單明細的預存程序
CREATE PROCEDURE add_order_item(
  IN p_order_id INT,
  IN p_product_id INT,
  IN p_quantity INT,
  IN p_unit_price DECIMAL(10, 2),
  OUT p_success BOOLEAN
)
BEGIN
  -- 宣告變數
  DECLARE v_order_status VARCHAR(20);
  DECLARE exit handler for sqlexception
  BEGIN
    ROLLBACK;
    SET p_success = FALSE;
  END;
  
  -- 檢查訂單狀態
  -- 只有 pending 狀態的訂單才能新增明細
  SELECT status INTO v_order_status
  FROM orders
  WHERE order_id = p_order_id;
  
  -- 條件判斷
  IF v_order_status = 'pending' THEN
    START TRANSACTION;
    
    -- 插入訂單明細
    -- 觸發器會自動更新訂單總金額
    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    VALUES (p_order_id, p_product_id, p_quantity, p_unit_price);
    
    COMMIT;
    SET p_success = TRUE;
  ELSE
    -- 訂單狀態不允許修改
    SET p_success = FALSE;
  END IF;
END//

-- 建立訂單確認的預存程序
-- 包含業務規則檢查
CREATE PROCEDURE confirm_order(
  IN p_order_id INT,
  OUT p_result VARCHAR(100)
)
BEGIN
  DECLARE v_total_amount DECIMAL(10, 2);
  DECLARE v_item_count INT;
  DECLARE exit handler for sqlexception
  BEGIN
    ROLLBACK;
    SET p_result = '訂單確認失敗：資料庫錯誤';
  END;
  
  START TRANSACTION;
  
  -- 檢查訂單是否有明細
  SELECT COUNT(*) INTO v_item_count
  FROM order_items
  WHERE order_id = p_order_id;
  
  -- 業務規則檢查
  IF v_item_count = 0 THEN
    SET p_result = '訂單確認失敗：訂單沒有任何明細';
    ROLLBACK;
  ELSE
    -- 取得訂單總金額
    SELECT total_amount INTO v_total_amount
    FROM orders
    WHERE order_id = p_order_id;
    
    -- 檢查金額是否合理
    IF v_total_amount <= 0 THEN
      SET p_result = '訂單確認失敗：訂單金額不正確';
      ROLLBACK;
    ELSE
      -- 更新訂單狀態
      UPDATE orders
      SET status = 'confirmed'
      WHERE order_id = p_order_id;
      
      COMMIT;
      SET p_result = CONCAT('訂單確認成功，總金額：', v_total_amount);
    END IF;
  END IF;
END//

DELIMITER ;

-- 呼叫預存程序的範例
-- 建立新訂單
CALL create_order(1, @new_order_id);
SELECT @new_order_id AS order_id;

-- 新增訂單明細
CALL add_order_item(@new_order_id, 101, 2, 1500.00, @success);
CALL add_order_item(@new_order_id, 102, 1, 2500.00, @success);

-- 確認訂單
CALL confirm_order(@new_order_id, @result);
SELECT @result AS confirmation_result;

資料完整性約束的實務應用

資料完整性是資料庫設計的核心考量，透過各種約束機制可以確保資料的正確性與一致性。主鍵約束保證每筆記錄都有唯一的識別碼，外鍵約束維護資料表之間的參照完整性，唯一約束防止重複資料，而檢查約束則限制欄位值必須符合特定條件。這些約束在資料庫層實施，比在應用程式層檢查更可靠，因為無論資料從哪個來源進入資料庫，都必須遵守這些規則。

外鍵約束的設計需要考慮刪除與更新時的級聯行為。CASCADE 選項會在父記錄被刪除或更新時，自動刪除或更新所有相關的子記錄。SET NULL 選項則會將外鍵欄位設為 NULL。RESTRICT 與 NO ACTION 選項則會拒絕刪除或更新有相關子記錄的父記錄。選擇哪種級聯行為取決於業務邏輯的需求。

-- 建立完整的產品與庫存管理系統
-- 展示各種完整性約束的應用

-- 產品類別資料表
CREATE TABLE categories (
  category_id INT AUTO_INCREMENT PRIMARY KEY,
  -- 類別名稱必須唯一
  category_name VARCHAR(100) NOT NULL UNIQUE,
  description TEXT,
  -- 檢查約束：確保類別名稱不為空字串
  CHECK (LENGTH(TRIM(category_name)) > 0)
) ENGINE=InnoDB;

-- 產品資料表
CREATE TABLE products (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(200) NOT NULL,
  -- 產品編號必須唯一
  sku VARCHAR(50) NOT NULL UNIQUE,
  category_id INT NOT NULL,
  -- 單價必須大於零
  unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0),
  -- 庫存數量不能為負數
  stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
  -- 安全庫存量設定
  reorder_level INT DEFAULT 10 CHECK (reorder_level >= 0),
  -- 產品狀態：active, discontinued
  status VARCHAR(20) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  -- 外鍵約束
  -- 產品必須關聯到存在的類別
  -- RESTRICT：如果類別還有產品，就不能刪除類別
  FOREIGN KEY (category_id) REFERENCES categories(category_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  
  -- 檢查約束：狀態只能是特定值
  CHECK (status IN ('active', 'discontinued')),
  
  -- 複合唯一約束：確保同一類別中產品名稱不重複
  UNIQUE KEY uk_category_product (category_id, product_name)
) ENGINE=InnoDB;

-- 供應商資料表
CREATE TABLE suppliers (
  supplier_id INT AUTO_INCREMENT PRIMARY KEY,
  supplier_name VARCHAR(200) NOT NULL,
  -- 統一編號必須唯一
  tax_id VARCHAR(20) NOT NULL UNIQUE,
  contact_email VARCHAR(100),
  phone VARCHAR(20),
  -- 檢查 email 格式
  CHECK (contact_email LIKE '%@%.%')
) ENGINE=InnoDB;

-- 產品供應商關聯表（多對多關係）
CREATE TABLE product_suppliers (
  product_id INT NOT NULL,
  supplier_id INT NOT NULL,
  -- 供應價格
  supply_price DECIMAL(10, 2) NOT NULL CHECK (supply_price > 0),
  -- 供應商產品編號
  supplier_sku VARCHAR(50),
  -- 複合主鍵：產品與供應商的組合必須唯一
  PRIMARY KEY (product_id, supplier_id),
  
  -- 外鍵約束
  -- CASCADE：產品或供應商被刪除時，自動刪除關聯記錄
  FOREIGN KEY (product_id) REFERENCES products(product_id)
    ON DELETE CASCADE,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

@startuml
!define PLANTUML_FORMAT svg
!theme _none_

skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 14
skinparam minClassWidth 100

entity "categories\n產品類別" as cat {
  * category_id : INT <<PK>>
  --
  * category_name : VARCHAR(100) <<UK>>
  description : TEXT
}

entity "products\n產品資料" as prod {
  * product_id : INT <<PK>>
  --
  * product_name : VARCHAR(200)
  * sku : VARCHAR(50) <<UK>>
  * category_id : INT <<FK>>
  * unit_price : DECIMAL(10,2)
  * stock_quantity : INT
  reorder_level : INT
  status : VARCHAR(20)
}

entity "suppliers\n供應商" as sup {
  * supplier_id : INT <<PK>>
  --
  * supplier_name : VARCHAR(200)
  * tax_id : VARCHAR(20) <<UK>>
  contact_email : VARCHAR(100)
  phone : VARCHAR(20)
}

entity "product_suppliers\n產品供應商關聯" as ps {
  * product_id : INT <<PK,FK>>
  * supplier_id : INT <<PK,FK>>
  --
  * supply_price : DECIMAL(10,2)
  supplier_sku : VARCHAR(50)
}

cat ||--o{ prod : "一對多"
prod ||--o{ ps : "多對多"
sup ||--o{ ps : "多對多"

note right of cat
  完整性約束：
  - 唯一名稱約束
  - 非空檢查
end note

note right of prod
  完整性約束：
  - 價格必須大於零
  - 庫存不能為負數
  - 狀態值限制
  - 複合唯一鍵
end note

note right of ps
  級聯行為：
  - DELETE CASCADE
  - 自動清理關聯
end note

}
}
}
@enduml

檢視的應用場景

檢視是基於一個或多個資料表的虛擬資料表,它不實際儲存資料,而是在查詢時動態產生結果。檢視的主要用途包括簡化複雜查詢、強化資料安全性,以及提供資料的不同展示方式。透過檢視可以隱藏底層資料表的複雜結構,只暴露使用者需要的欄位,這在多使用者環境中特別重要。

檢視也可以用於實施列層級的安全控制,例如建立一個檢視只顯示特定部門的資料,然後將查詢權限授予該檢視而非底層資料表。某些檢視支援更新操作,但有許多限制,例如檢視中不能包含聚合函數、DISTINCT、GROUP BY 等子句。一般建議將檢視用於查詢而非更新。

-- 建立各種類型的檢視

-- 簡單檢視：產品清單
-- 隱藏內部欄位,只顯示必要資訊
CREATE VIEW v_product_list AS
SELECT 
  p.product_id,
  p.product_name,
  p.sku,
  c.category_name,
  p.unit_price,
  p.stock_quantity,
  p.status
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
WHERE p.status = 'active';

-- 複雜檢視：庫存警示
-- 顯示低於安全庫存的產品
CREATE VIEW v_low_stock_alert AS
SELECT 
  p.product_id,
  p.product_name,
  p.sku,
  c.category_name,
  p.stock_quantity,
  p.reorder_level,
  -- 計算缺貨數量
  (p.reorder_level - p.stock_quantity) AS shortage_quantity,
  -- 取得最便宜的供應商
  (SELECT s.supplier_name
   FROM product_suppliers ps
   INNER JOIN suppliers s ON ps.supplier_id = s.supplier_id
   WHERE ps.product_id = p.product_id
   ORDER BY ps.supply_price
   LIMIT 1) AS cheapest_supplier
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
WHERE p.stock_quantity < p.reorder_level
  AND p.status = 'active'
ORDER BY shortage_quantity DESC;

-- 聚合檢視：訂單統計
CREATE VIEW v_order_statistics AS
SELECT 
  DATE(o.order_date) AS order_date,
  o.status,
  COUNT(*) AS order_count,
  SUM(o.total_amount) AS total_revenue,
  AVG(o.total_amount) AS average_order_value,
  COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
GROUP BY DATE(o.order_date), o.status;

-- 查詢檢視
-- 使用方式與一般資料表相同
SELECT * FROM v_low_stock_alert;

-- 刪除檢視
DROP VIEW IF EXISTS v_product_list;

從觸發器的自動化機制到預存程序的邏輯封裝,從完整性約束的資料保護到檢視的安全控制,MySQL 提供了完整的工具集來建構企業級的資料庫系統。這些進階功能的正確運用能夠大幅提升系統的可靠性與維護性,但同時也需要謹慎設計以避免過度複雜化。

在實務應用中,建議採用漸進式的方法導入這些功能。先從最基本的外鍵約束與索引開始,確保資料的參照完整性與查詢效能。當業務邏輯變得複雜時,再考慮使用觸發器來自動化特定操作,或是使用預存程序來封裝常用的資料處理流程。檢視則適合用於簡化複雜查詢與實施資料安全策略。

MySQL Workbench 等圖形化工具雖然能夠簡化這些進階功能的管理,但深入理解底層的 SQL 語法與運作機制仍然至關重要。透過命令列操作不僅能夠更精確地控制資料庫行為,也有助於撰寫自動化腳本與進行版本控制。定期檢視觸發器與預存程序的執行效能,適時調整索引策略,並保持資料庫結構的文件完整,這些都是專業資料庫管理者應該養成的習慣。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。