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

MySQL 帶參數動態存儲過程實戰指南

編輯：MySQL 綜合教學

MySQL 存儲過程支援動態 SQL (`PREPARE`/`EXECUTE`/`DEALLOCATE PREPARE`)，配合輸入參數可實現靈活的條件查詢、動態插入、分表路由等進階場景。本文詳解語法細節、注入防範、效能考量與最佳實踐。

核心語法三件套

PREPARE stmt_name FROM @sql_string;  -- 預處理 (解析、優化、生成執行計劃)
EXECUTE stmt_name [USING @var1, @var2, ...];  -- 執行 (可傳參數)
DEALLOCATE PREPARE stmt_name;  -- 釋放 (必須釋放，否則會話結束前佔用 prepared_statement 配額)

場景一：動態條件查詢 (替代應用層拼 SQL)

CREATE PROCEDURE GetUsersDynamic(
  IN p_where_condition VARCHAR(500),  -- 如 'age <= 30 AND status = 1'
  IN p_order_by VARCHAR(100)          -- 如 'age DESC' 或 '' (不排序)
)
BEGIN
  DECLARE v_sql TEXT DEFAULT '';
  DECLARE v_order_clause VARCHAR(120) DEFAULT '';

  -- 安全處理 ORDER BY：白名單驗證，防注入
  IF p_order_by != '' THEN
    -- 簡單白名單：僅允許指定欄位 + ASC/DESC
    IF p_order_by REGEXP '^(age|created_at|username)( ASC| DESC)?$' THEN
      SET v_order_clause = CONCAT(' ORDER BY ', p_order_by);
    ELSE
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid ORDER BY clause';
    END IF;
  END IF;

  -- 建構動態 SQL (WHERE 條件同樣需白名單或參數化，此處示範拼接風險)
  SET v_sql = CONCAT(
    'SELECT id, username, age, created_at ',
    'FROM users ',
    'WHERE ', p_where_condition,
    v_order_clause
  );

  PREPARE stmt FROM v_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;

場景二：動態插入 (正確處理引號轉義)

CREATE PROCEDURE InsertUserDynamic(
  IN p_username VARCHAR(50),
  IN p_password_hash CHAR(60),
  IN p_age INT,
  IN p_created_at DATETIME(6)
)
BEGIN
  DECLARE v_sql TEXT;
  -- 關鍵：使用 QUOTE() 函數自動處理字串轉義與引號包裹
  -- QUOTE('abc') -> 'abc' ; QUOTE("it's") -> 'it\'s' ; QUOTE(NULL) -> NULL
  SET v_sql = CONCAT(
    'INSERT INTO users (username, password_hash, age, created_at) VALUES (',
    QUOTE(p_username), ',',
    QUOTE(p_password_hash), ',',
    p_age, ',',
    QUOTE(p_created_at), ')'
  );

  PREPARE stmt FROM v_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;

場景三：參數化動態 SQL (更安全、可復用執行計劃)

CREATE PROCEDURE GetUsersByAgeRange(
  IN p_min_age INT,
  IN p_max_age INT,
  IN p_limit INT
)
BEGIN
  -- 使用 ? 佔位符，EXECUTE 時 USING 綁定變數
  SET @sql = 'SELECT id, username, age FROM users WHERE age BETWEEN ? AND ? LIMIT ?';
  PREPARE stmt FROM @sql;
  EXECUTE stmt USING @p_min_age, @p_max_age, @p_limit;
  DEALLOCATE PREPARE stmt;
END;

**優勢**：

**防注入**：參數與 SQL 結構分離。
**計劃復用**：同一 `PREPARE` 陳述式在會話內可多次 `EXECUTE` 不同參數。
**類型安全**：驅動層處理類型轉換。

自定義函數配合：日期格式化

CREATE FUNCTION FormatDate(p_dt DATETIME(6))
RETURNS VARCHAR(20) DETERMINISTIC
RETURN DATE_FORMAT(p_dt, '%Y-%m-%d %H:%i:%s');

關鍵限制與避坑

限制	說明	對策
動態 SQL 不能直接使用存儲過程參數	`PREPARE` 只能引用用戶變數 (`@var`)，不能直接用 `p_param`	先 `SET @var = p_param;` 再在 SQL 中用 `?` 或 `QUOTE(@var)`
預處理語句作用域 = 會話	同一會話內 `PREPARE` 同名會報錯，不同會話隔離	用唯一名稱 (如 `CONCAT('stmt_', CONNECTION_ID(), '_', UUID_SHORT())`) 或用畢 `DEALLOCATE`
最大預處理數限制	`max_prepared_stmt_count` (預設 16382)	及時 `DEALLOCATE`，監控 `Prepared_stmt_count` 狀態
動態 SQL 內不可用 `DECLARE` 變數	只能用用戶變數 `@var`	同上
DDL 操作不能參數化	`PREPARE` 不支援 `CREATE`/`ALTER`/`DROP` 等 DDL 的參數綁定	只能拼接字串 (極度謹慎，白名單驗證)
執行計劃緩存不跨會話	每個連線獨立準備	長連線池 (HikariCP) 可受益於會話級計劃復用

防注入鐵律：白名單 > 轉義 > 拼接

白名單驗證：`ORDER BY`、`WHERE` 欄位名、表名、運算符 — 只允許預定義集合。
參數化：值類輸入 **永遠用 `?` + `USING`** 或 `QUOTE()`。
拒絕拼接：除非極少數 DDL 場景，且經過嚴格白名單驗證。

效能建議

**簡單查詢優先用參數化預處理**，而非動態拼接。
**避免在高頻熱點路徑使用存儲過程動態 SQL** — 網路往返、解析優化、計劃緩存失效開銷大，應用層構建參數化 SQL 通常更快。
**批次操作用多值 `INSERT` 或 `LOAD DATA`**，不要循環 `CALL` 存儲過程。
**監控**：`SHOW GLOBAL STATUS LIKE 'Prepared_stmt_count';`、`Com_prepare_sql`、`Com_execute_sql`。