存儲過程基礎語法結構
DELIMITER //
CREATE PROCEDURE proc_name ([IN/OUT/INOUT] param_name type, ...)
BEGIN
-- 聲明變量
DECLARE var_name type [DEFAULT value];
-- 流程控制
IF condition THEN ... ELSEIF ... ELSE ... END IF;
CASE ... WHEN ... THEN ... ELSE ... END CASE;
LOOP ... END LOOP;
WHILE condition DO ... END WHILE;
REPEAT ... UNTIL condition END REPEAT;
-- 錯誤處理
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @err = 1;
-- 動態 SQL
SET @sql = CONCAT('SELECT * FROM ', table_name);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 返回結果集
SELECT * FROM some_table;
END //
DELIMITER ;
參數模式詳解
| 模式 | 方向 | 可修改 | 調用時必須為變量 |
|---|
| IN | 輸入 | 否 | 否（可為表達式） |
| OUT | 輸出 | 是 | 是 |
| INOUT | 雙向 | 是 | 是 |
實戰案例：分頁查詢存儲過程
CREATE PROCEDURE sp_page_query(
IN p_table VARCHAR(64),
IN p_where VARCHAR(500),
IN p_order VARCHAR(100),
IN p_page INT,
IN p_size INT,
OUT p_total BIGINT
)
BEGIN
DECLARE v_offset INT;
DECLARE v_sql TEXT;
-- 計算總數
SET @count_sql = CONCAT('SELECT COUNT(*) INTO @total FROM ', p_table, ' WHERE ', p_where);
PREPARE stmt FROM @count_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET p_total = @total;
-- 分頁查詢
SET v_offset = (p_page - 1) * p_size;
SET v_sql = CONCAT('SELECT * FROM ', p_table, ' WHERE ', p_where, ' ORDER BY ', p_order, ' LIMIT ', v_offset, ',', p_size);
PREPARE stmt FROM v_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
調試技巧與工具
- SELECT ... INTO 變量：中間結果存入會話變量
@var，調用後 SELECT @var 檢查。
- 信息輸出：
SELECT 'DEBUG: ', var_name; 結果集直接可見。
- Workbench/Navicat/DBeaver：內置調試器，支持斷點、單步執行、變量觀察。
- 性能分析：
SHOW PROFILE FOR QUERY n 或 EXPLAIN 動態 SQL。
最佳實踐清單
- ✅ 使用
DELIMITER 避免分號衝突
- ✅ 參數、變量命名加前綴（
p_、v_）區分
- ✅ 動態 SQL 必須防注入：識別符用
QUOTE() 或白名單校驗
- ✅ 避免在存儲過程中處理大量業務邏輯，僅做數據層聚合
- ✅ 明確指定
SQL SECURITY DEFINER/INVOKER 權限上下文
- ✅ 版本控制：存儲過程代碼納入 Git，通過遷移腳本部署
常見陷阱
| 陷阱 | 現象 | 解決 |
|---|
| 游標未關閉 | 內存洩漏、報錯 | CLOSE cursor; DEALLOCATE PREPARE stmt; 配對使用 |
| 隱式提交 | DDL 導致事務提交 | 避免存儲過程中混用 DDL |
| 權限不足 | 調用者無執行權限 | GRANT EXECUTE ON PROCEDURE db.proc TO user; |
| 字符集亂碼 | 動態 SQL 拼接中文 | 連接層 SET NAMES utf8mb4，表列字符集一致 |
總結
MySQL 存儲過程將數據邏輯下沉，減少網絡往返、封裝業務規則。掌握語法細節、動態 SQL 安全用法、調試手段與工程化部署流程，能讓存儲過程成為性能優化與安全管控的利器，而非維護負擔。
