MySQL 存儲過程、函數與觸發器完全指南：語法、示例與最佳實踐

一、 常用內建函數速查表

1. 字符串函數

2. 數學函數

3. 日期時間函數

INTERVAL 可用單位：SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR 及組合（DAY_HOUR 等）。

二、 存儲過程

1. 創建與調用

DELIMITER //
DROP PROCEDURE IF EXISTS `PROC_TEST`//
CREATE PROCEDURE `PROC_TEST`(TABLE_NAME VARCHAR(20), NUM INT)
BEGIN
  SET @sql = CONCAT('SELECT * FROM ', TABLE_NAME, ' LIMIT ', NUM);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

-- 調用
CALL PROC_TEST('user', 20);

注意：存儲過程不支持直接用變量作表名，需用動態 SQL（PREPARE/EXECUTE）。

2. 刪除

DROP PROCEDURE IF EXISTS `PROC_TEST`;

三、 觸發器

1. 語法結構

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
trigger_body

• 同一表同一事件只能有一個觸發器。
• NEW：INSERT/UPDATE 後的新行；OLD：UPDATE/DELETE 前的舊行。
• 觸發器體內不能調用返回數據給客戶端的語句（如 SELECT），不能使用顯式/隱式事務控制語句。

2. 實戰示例

場景：tab1 異動同步至 tab2

-- AFTER INSERT
DROP TRIGGER IF EXISTS `t_afterinsert_on_tab1`;
CREATE TRIGGER `t_afterinsert_on_tab1`
AFTER INSERT ON `tab1`
FOR EACH ROW
BEGIN
  INSERT INTO `tab2`(`tab2_id`) VALUES (NEW.`tab1_id`);
END;

-- AFTER DELETE
DROP TRIGGER IF EXISTS `t_afterdelete_on_tab1`;
CREATE TRIGGER `t_afterdelete_on_tab1`
AFTER DELETE ON `tab1`
FOR EACH ROW
BEGIN
  DELETE FROM `tab2` WHERE `tab2_id` = OLD.`tab1_id`;
END;

3. 刪除觸發器

DROP TRIGGER [schema_name.]trigger_name;

四、 進階話題與最佳實踐

1. 存儲過程 vs 應用層邏輯

• 優點：減少網絡往返、封裝業務邏輯、權限控制細粒度。
• 缺點：難以版本控制、調試困難、資料庫 CPU 壓力增大、可移植性差。
• 建議：核心業務邏輯留在應用層，存儲過程僅用於高頻、簡單、數據密集型操作（如批量統計、數據清洗）。

2. 觸發器的隱性風險

• **隱式邏輯**：應用層代碼無感知，排查問題困難。
• **性能放大器**：批量操作觸發器逐行執行，嚴重拖慢寫入。
• **遞歸陷阱**：觸發器中操作同一表再次觸發（需用會話變量標記防重入）。
• 建議：能不用盡量不用；必須用時，保持極簡、文檔化、加監控。

3. 權限與安全

• 創建觸發器需 TRIGGER 權限（5.1.6+），存儲過程需 CREATE ROUTINE。
• 定義者（DEFINER）權限執行，調用者權限需顯式指定 SQL SECURITY INVOKER。

五、 總結

MySQL 存儲過程、函數、觸發器是伺服端編程的三駕馬車。熟練掌握內建函數能寫出簡潔高效的 SQL；存儲過程適用於數據密集型封裝；觸發器是雙刃劍，謹慎使用。原則：業務邏輯上層化，資料庫專注存儲與查詢。