MySQL 存儲過程性能優勢原理與核心語法實戰

為什麼存儲過程高效？

MySQL 執行 SQL 經歷：語法解析 → 編譯優化 → 執行 → 返回結果。存儲過程將 SQL 集合預編譯存放於服務端，調用時跳過解析編譯階段，直接執行，並大幅減少網絡往返，因此具備：

• 功能與靈活性增強（支持流程控制、變量、異常處理）；
• 執行速度更快（省去重複編譯開銷）；
• 網絡流量極低（僅傳輸 CALL 語句與結果集）。

核心語法速查

創建存儲過程

CREATE PROCEDURE sp_name (param1 type, param2 type, ...)
routine_body

-- 參數模式
IN    -- 輸入參數（默認）
OUT   -- 輸出參數
INOUT -- 輸入輸出參數

-- 過程體：合法 SQL 組合（不含建庫建表）

三類參數實戰示例

1. 無參數

CREATE PROCEDURE select_version() SELECT VERSION();

2. IN 參數

DELIMITER //
CREATE PROCEDURE remove_nav(IN id INT UNSIGNED)
BEGIN
  DELETE FROM yiqi_navigate WHERE navid = id;
END //
DELIMITER ;

CALL remove_nav(10);

⚠️ 注意：參數名勿與列名重名，否則 WHERE navid = id 會匹配列導致全表刪除。

3. IN + OUT 參數

DELIMITER //
CREATE PROCEDURE rem_nav_return(IN id INT UNSIGNED, OUT name VARCHAR(64))
BEGIN
  DELETE FROM yiqi_navigate WHERE navid = id;
  SELECT COUNT(navid) INTO name FROM yiqi_navigate;  -- 結果賦給 OUT 參數
END //
DELIMITER ;

CALL rem_nav_return(9, @nums);  -- @nums 為客戶端用戶變量

管理操作

-- 刪除
DROP PROCEDURE remove_link;

-- 修改特征（不可改主體，需 Drop 再 Create）
ALTER PROCEDURE proc_name [characteristic...];

進階：為什麼要修改 DELIMITER？

默認分隔符 ; 會過早終止 CREATE PROCEDURE 語句。使用 DELIMITER // 將語句結束符臨時改為 //，待過程體內所有 ; 執行完畢，再用 // 結束創建，最後恢復 DELIMITER ;。

最佳實踐建議

1. 命名規範：避開內置函數名，建議前綴 sp_ 或 proc_；
2. 參數命名：加前綴 p_，與列名區分，防止歧義；
3. 事務控制：複雜業務在過程體內顯式 START TRANSACTION / COMMIT / ROLLBACK；
4. 權限最小化：SQL SECURITY DEFINER（默認）以創建者權限執行，INVOKER 以調用者權限執行，根據安全策略選擇；
5. 版本管理：存儲過程代碼納入版本控制，發布時通過遷移腳本部署，避免手動操作差異。