MySQL 存儲程序完整入門：函數、過程、觸發器、事件與複合語句詳解

存儲程序對象類型

MySQL 5.0+ 支援四類伺服器端存儲對象：

核心優勢

• 減少網路開銷：邏輯在伺服器端執行，僅傳遞呼叫指令
• 封裝複雜邏輯：支援變數、流程控制（IF/CASE/LOOP/WHILE/REPEAT）、錯誤處理
• 安全性：DEFINER/INVOKER 權限模型，實現資料存取最小權限
• 模組化復用：建立函數庫供多應用共享

複合語句與分隔符

存儲程序主體為 複合語句：BEGIN ... END 區塊包含多條 SQL，語句間以 ; 分隔。但 ; 也是 mysql 客戶端預設分隔符，定義時必須暫時變更：

DELIMITER $$

CREATE PROCEDURE demo()
BEGIN
    DECLARE v INT DEFAULT 0;
    WHILE v < 5 DO
        SELECT v;
        SET v = v + 1;
    END WHILE;
END$$

DELIMITER ;

原則：主體內含 ; 時，定義前必須 DELIMITER 新符號，結束後恢復 DELIMITER ;。

存儲函數 (FUNCTION)

建立語法

CREATE FUNCTION count_born_in_year(p_year INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE cnt INT;
    SELECT COUNT(*) INTO cnt FROM president WHERE YEAR(birth) = p_year;
    RETURN cnt;
END

• RETURNS：宣告返回型別
• DETERMINISTIC：相同輸入必得相同輸出（二進制日誌安全需要）
• READS SQL DATA：聲明只讀資料（或 NO SQL/MODIFIES SQL DATA）
• 主體至少一條 RETURN

呼叫方式

SELECT count_born_in_year(1950);  -- 可用於表達式

存儲過程 (PROCEDURE)

建立語法

CREATE PROCEDURE show_born_in_year(p_year INT)
BEGIN
    SELECT name, birth FROM president WHERE YEAR(birth) = p_year;
END

• 無 RETURNS、無 RETURN 值
• 可透過 OUT/INOUT 參數回傳多值
• 可返回多個結果集

呼叫方式

CALL show_born_in_year(1950);

參數模式

OUT 參數範例

CREATE PROCEDURE count_by_sex(OUT p_male INT, OUT p_female INT)
BEGIN
    SELECT COUNT(*) INTO p_male FROM student WHERE sex='M';
    SELECT COUNT(*) INTO p_female FROM student WHERE sex='F';
END

CALL count_by_sex(@m, @f);
SELECT @m, @f;

權限模型

• 建立需 CREATE ROUTINE 權限
• 預設自動授予建立者 EXECUTE、ALTER ROUTINE
• automatic_sp_privileges=0 可關閉自動授權
• 二進制日誌啟用時：函數需 DETERMINISTIC/NO SQL/READS SQL DATA 其中之一，否則需 SUPER 權限或開啟 log_bin_trust_function_creators=1

觸發器 (TRIGGER)

建立語法

CREATE TRIGGER trg_bi_user
BEFORE INSERT ON `user`
FOR EACH ROW
BEGIN
    IF NEW.email NOT LIKE '%@%' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email';
    END IF;
    SET NEW.created_at = NOW();
END

• 時機：BEFORE/AFTER
• 事件：INSERT/UPDATE/DELETE
• 行級：FOR EACH ROW（MySQL 僅支援行級）
• 存取新/舊值：NEW.col、OLD.col

事件 (EVENT)

建立語法

CREATE EVENT ev_cleanup_logs
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

• 需啟用 event_scheduler=ON
• 支援一次性/週期性排程

錯誤處理機制

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
    INSERT INTO error_log VALUES (@sqlstate, @msg, NOW());
END;

最佳實踐建議

1. 命名規範：fn_ 函數、sp_ 過程、trg_ 觸發器、ev_ 事件
2. 函數不可修改呼叫它的查詢所讀寫的表（過程無此限制，除非被函數呼叫）
3. 避免過度使用：簡單邏輯應用層處理；跨庫/跨表複雜事務、批次維護、審計適合存儲程序
4. 版本控管：DDL 腳本納入 Git，避免線上手改
5. 效能監控：SHOW PROFILE、performance_schema 分析執行耗時

總結

MySQL 存儲程序提供完整的程式化能力（變數、流控、錯誤處理、權限模型），核心價值在於邏輯下沉、網路減載、安全封裝。掌握 DELIMITER、參數模式、權限與二進制日誌限制，是正確使用的關鍵。現代架構下，建議將業務核心邏輯留在應用層，存儲程序專注於資料層面的批次、維護、審計、同步等場景。