MySQL 存儲過程與函數完全指南：語法、特性、權限、二進制日誌安全與調試技巧

創建語法對比

-- 存儲過程
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

-- 函數
CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

關鍵差異

Characteristic 子句深度解析（核心考點）

CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20), OUT pcount INT)
    READS SQL DATA
    SQL SECURITY INVOKER
    COMMENT '按部門統計人數'
BEGIN
    SELECT COUNT(id) INTO pcount FROM Employee WHERE depid = pdepid;
END;

各特性含義

• LANGUAGE SQL：語句為 SQL（預留未來擴展）
• DETERMINISTIC / NOT DETERMINISTIC：相同輸入是否必然產生相同輸出。影響優化器決策，默認 NOT DETERMINISTIC
• SQL 數據訪問類型（四選一）：
  • CONTAINS SQL：不讀寫表數據
  • NO SQL：不含 SQL 語句
  • READS SQL DATA：只讀（如 SELECT）
  • MODIFIES SQL DATA：有寫入（INSERT/UPDATE/DELETE）
• SQL SECURITY：
  • DEFINER：以創建者權限執行（默認）
  • INVOKER：以調用者權限執行（更安全，推薦）
• COMMENT：備註，SHOW PROCEDURE STATUS 可見

二進制日誌安全：ERROR 1418 根因與雙重解法

報錯場景

主從複制環境開啟 log_bin，創建函數未顯式聲明確定性/數據訪問類型：

Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA 
in its declaration and binary logging is enabled

原因

函數在主庫執行結果可能與從庫不一致（如 UUID()、NOW()），導致主從數據漂移。MySQL 默認阻攔此類風險。

解法一：放寬限制（有風險）

SET GLOBAL log_bin_trust_function_creators = 1;
-- 或配置文件 [mysqld] log_bin_trust_function_creators = 1
-- 主從雙方均需設置，重啟生效

解法二：規範聲明（推薦，零風險）

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

明確告知 MySQL：此函數確定性、只讀數據 → 主從執行結果必然一致 → 安全寫入 Binlog。

查看與調試

-- 狀態
SHOW PROCEDURE STATUS LIKE 'Pro_Employee'\G;

-- 創建語句
SHOW CREATE PROCEDURE Pro_Employee\G;

-- 詳細元數據
SELECT * FROM information_schema.ROUTINES 
WHERE ROUTINE_NAME = 'Pro_Employee'\G;

修改與刪除

ALTER PROCEDURE proc_name [characteristic ...];
DROP PROCEDURE [IF EXISTS] proc_name;
-- 函數同理：ALTER/DROP FUNCTION

最佳實踐清單

1. 生產環境必須顯式聲明 DETERMINISTIC + 數據訪問類型
2. 優先使用 SQL SECURITY INVOKER 實現最小權限
3. 複雜邏輯拆分：存儲過程處理流程控制，函數處理純計算
4. 避免在存儲過程中使用動態 SQL（難維護、難優化）
5. 版本管理：定期 mysqldump --routines 導出存檔