MySQL 存儲過程與函數全解析：語法、特性、權限模型與複製陷阱

MySQL 存儲過程與函數全解析：語法、特性、權限模型與複製陷阱

存儲過程（Procedure）與函數（Function）是 MySQL 服務端程式化的核心。本文系統梳理創建語法、特性子句、權限模型（DEFINER/INVOKER）、查看元數據、以及主從複製環境下的 log_bin_trust_function_creators 陷阱與解決方案。

核心語法差異速查

characteristic 子句深度解讀

CREATE PROCEDURE sp_name ([params])
[characteristic ...] routine_body

characteristic:
  LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

關鍵屬性對照表

複製環境陷阱與解決方案

報錯： Error 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA...

原因： 啟用 log_bin 時，MySQL 強制要求函數顯式聲明確定性或只讀特性，防止主從數據漂移。

方案一：放寬檢查（有風險）

SET GLOBAL log_bin_trust_function_creators = 1;
-- 永久生效需寫入 my.cnf [mysqld] 區段

⚠️ 僅在確認函數邏輯在主從完全一致時使用，否則可能導致複製錯誤。

方案二：顯式聲明特性（推薦）

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

存儲過程同理，建議在創建腳本中強制包含 DETERMINISTIC + READS SQL DATA / MODIFIES SQL DATA。

元數據查看三板斧

-- 1. 狀態概覽
SHOW PROCEDURE STATUS LIKE 'sp_demo'\G;

-- 2. 創建語句（含 characteristic）
SHOW CREATE PROCEDURE sp_demo\G;

-- 3. 完整元數據（information_schema）
SELECT * FROM information_schema.ROUTINES 
WHERE ROUTINE_SCHEMA = 'mydb' AND ROUTINE_NAME = 'sp_demo'\G;

修改與刪除

-- 僅能修改 characteristic，不可改 routine_body
ALTER PROCEDURE sp_name [characteristic ...];

-- 刪除
DROP PROCEDURE [IF EXISTS] sp_name;
DROP FUNCTION [IF EXISTS] fn_name;

最佳實踐清單

• 所有例程強制顯式聲明 DETERMINISTIC + 數據訪問特性 + SQL SECURITY。
• 優先使用 SQL SECURITY INVOKER，遵循最小權限原則。
• 例程體內禁用動態 SQL（PREPARE），若必須動態則嚴格驗證輸入防注入。
• 主從環境下，主庫創建例程後，檢查從庫 SHOW REPLICA STATUS 確認無複製錯誤。
• 大型例程拆分為小模塊，便於單元測試與代碼審查。