MySQL 存儲過程與函數入門：創建、調用、權限與限制詳解

核心區別

創建語法

存儲過程

CREATE PROCEDURE sp_get_user (IN uid INT, OUT uname VARCHAR(50))
BEGIN
    SELECT username INTO uname FROM users WHERE id = uid;
END;

函數

CREATE FUNCTION fn_get_username (uid INT)
RETURNS VARCHAR(50)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE uname VARCHAR(50);
    SELECT username INTO uname FROM users WHERE id = uid;
    RETURN uname;
END;

關鍵聲明

• DETERMINISTIC：相同輸入必得相同輸出，優化器可緩存結果。
• NOT DETERMINISTIC：含隨機/時間等非確定邏輯。
• READS SQL DATA：只讀數據。
• MODIFIES SQL DATA：修改數據 (過程默認，函數需聲明)。
• CONTAINS SQL：不讀寫表，純計算。
• NO SQL：無 SQL 語句。

調用示例

-- 過程
CALL sp_get_user(1, @name);
SELECT @name;

-- 函數
SELECT fn_get_username(1);
SELECT * FROM users WHERE name = fn_get_username(2);

權限模型

• 創建：CREATE ROUTINE 權限。
• 調用：EXECUTE 權限。
• 查看定義：SHOW CREATE PROCEDURE/FUNCTION。
• 刪除：DROP PROCEDURE/FUNCTION 需 ALTER ROUTINE。

定義者 vs 調用者權限

CREATE DEFINER = 'root'@'%' PROCEDURE sp_xxx ...  -- 默認，以創建者權限執行
CREATE DEFINER = 'app_user'@'%' PROCEDURE sp_xxx SQL SECURITY INVOKER ...  -- 以調用者權限執行

生產環境推薦 SQL SECURITY INVOKER，避免權限提升風險。

常見限制與避坑

1. 函數中不能使用動態 SQL (PREPARE)。
2. 函數不能顯式 COMMIT/ROLLBACK。
3. 過程/函數內不能使用 LOAD DATA INFILE。
4. 遞歸深度受 max_sp_recursion_depth 限制 (默認 0 禁用)。
5. MySQL 不支援包/包體分離 (Oracle 風格)，所有邏輯在單一 CREATE 中。