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

MySQL 存儲過程與函數完整指南：創建語法、特性聲明、權限模型與復制安全

編輯：MySQL 綜合教學

存儲過程 vs 函數：核心差異速查

特性	存儲過程 (PROCEDURE)	函數 (FUNCTION)
返回值	無 (通過 OUT/INOUT 參數)	必須有 RETURN
參數類型	IN / OUT / INOUT	僅 IN
調用方式	`CALL proc_name(...)`	SQL 表達式中 `func_name(...)`
事務控制	可執行 START/COMMIT/ROLLBACK	不可顯式事務控制
動態 SQL	支援 PREPARE/EXECUTE	不支援
調用函數	可調用函數	不可調用存儲過程

一、創建語法完整解析

1. 存儲過程

CREATE FUNCTION func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    routine_body

func_parameter: param_name type  -- 僅 IN，無 OUT/INOUT

3. 關鍵特性聲明詳細說明 (characteristic)

特性	選項	含義	復制環境影響
DETERMINISTIC	DETERMINISTIC / NOT DETERMINISTIC	相同輸入是否必然產生相同輸出	復制環境必須顯式聲明，否則報錯 1418
SQL 數據訪問	CONTAINS SQL / NO SQL / READS SQL DATA / MODIFIES SQL DATA	聲明對數據的讀寫行為	復制環境必須顯式聲明，建議 READS SQL DATA
SQL SECURITY	DEFINER / INVOKER	執行權限上下文：創建者 vs 調用者	影響權限檢查，DEFINER 需 SUPER 權限
COMMENT	'string'	備註信息	無

復制環境報錯 1418 根因與解決

CREATE FUNCTION fn_get_count(p_id INT)
    RETURNS INT
    DETERMINISTIC
    READS SQL DATA
    BEGIN
        DECLARE v_cnt INT;
        SELECT COUNT(*) INTO v_cnt FROM tbl WHERE id = p_id;
        RETURN v_cnt;
    END

全局放寬 (風險自負)：

## 場景: root 建立過程，app_user 調用，操作表 sensitive_data (app_user 無權限)

## 1. SQL SECURITY DEFINER (預設)
CREATE PROCEDURE get_sensitive() SQL SECURITY DEFINER
SELECT * FROM sensitive_data;
-- app_user 調用 → 以 root 權限執行 → 成功 (權限提升風險)

## 2. SQL SECURITY INVOKER
CREATE PROCEDURE get_sensitive() SQL SECURITY INVOKER
SELECT * FROM sensitive_data;
-- app_user 調用 → 以 app_user 權限執行 → 失敗 (權限不足，安全)

最佳實踐： 預設使用 INVOKER，最小權限原則；僅在受控的管理類過程使用 DEFINER。

三、查看與管理元數據

操作	SQL	輸出重點
查看狀態	`SHOW PROCEDURE STATUS [LIKE 'pattern']`	Db、Name、Type、Definer、Security_type、Created
查看創建語句	`SHOW CREATE PROCEDURE sp_name`	完整 CREATE 語句、特性聲明
查看詳細信息	`SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='sp_name'`	所有元數據欄位、參數列表、SQL 模式
修改特性	`ALTER PROCEDURE sp_name [characteristic ...]`	僅能修改特性，不可修改邏輯/參數
刪除	`DROP PROCEDURE [IF EXISTS] sp_name`

四、實戰範例：帶完整特性聲明的存儲過程

DROP FUNCTION IF EXISTS fn_calc_age;
DELIMITER $$
CREATE FUNCTION fn_calc_age(p_birth_date DATE)
RETURNS INT
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
COMMENT '根據出生日期計算年齡'
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, p_birth_date, CURDATE());
END$$
DELIMITER ;

## 在 SQL 中直接使用
SELECT name, fn_calc_age(birth_date) AS age FROM employees;