您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 預存程序與函數差異深度解析：權限、回傳、調用、決定性四大維度

編輯：MySQL 綜合教學

核心差異對照表

維度	預存程序	函數
調用方式	`CALL sp_name(...)` 獨立語句	`SELECT func_name(...)` 嵌入 SQL 表達式
回傳值	無直接回傳；透過 `OUT/INOUT` 參數、結果集	必須回傳單一標量值 (或 TABLE 函數回傳表)
參數模式	`IN`、`OUT`、`INOUT`	僅 `IN` (輸入參數)
事務控制	可執行 `COMMIT/ROLLBACK`、`START TRANSACTION`	不可顯式控制事務
修改資料	可執行 `INSERT/UPDATE/DELETE/DDL`	不可修改表資料 (僅 8.0+ `NOT DETERMINISTIC` 允許副作用，但強烈不建議)
決定性	無限制	預設 `DETERMINISTIC`，相同輸入必得相同輸出
權限檢查	`SQL SECURITY DEFINER/INVOKER` (預設 DEFINER)	同預存程序，但通常用 `INVOKER`
建立權限	`CREATE ROUTINE`	`CREATE ROUTINE`
使用場景	複雜業務流程、批次作業、封裝多步驟邏輯	可重用運算、格式化、查詢片段、索引下推輔助

語法對比

-- 預存程序
CREATE PROCEDURE `sp_add_user`(
  IN p_name VARCHAR(32),
  IN p_email VARCHAR(128),
  OUT p_new_id BIGINT
)
BEGIN
  INSERT INTO `user` (`name`,`email`) VALUES (p_name, p_email);
  SET p_new_id = LAST_INSERT_ID();
END;

-- 函數
CREATE FUNCTION `fn_format_name`(p_name VARCHAR(32), p_title VARCHAR(10))
RETURNS VARCHAR(50)
DETERMINISTIC
NO SQL
RETURN CONCAT(p_title, ' ', p_name);

調用示範

-- 程序
CALL sp_add_user('Alice', 'alice@test.com', @id);
SELECT @id;

-- 函數
SELECT fn_format_name('Bob', 'Mr.') AS full_name;
-- 或用於 WHERE
SELECT * FROM `user` WHERE `name` = fn_format_name('Bob', 'Mr.');

選擇指南

需修改資料、多步驟、事務控制、多結果集 → 預存程序。
需嵌入 SELECT/WHERE/ORDER BY、單一運算、無副作用 → 函數。
需回傳表集合 (如拆分字串) → MySQL 8.0 CREATE FUNCTION ... RETURNS TABLE 或改用 VIEW/CTE。
避免在函數中執行寫入操作，違反決定性、難除錯、鎖爭用高。

進階：非確定性函數

CREATE FUNCTION `fn_rand_str`(len INT)
RETURNS VARCHAR(64)
NOT DETERMINISTIC  -- 聲明非確定性
READS SQL DATA
BEGIN
  RETURN SUBSTRING(MD5(RAND()), 1, len);
END;

警告：NOT DETERMINISTIC 會導致查詢快照失效、複製延遲、優化器無法緩存，生產環境極度謹慎使用。