MySQL 存儲過程動態 SQL 實戰：PREPARE/EXECUTE 實現運行時構建查詢

應用場景

存儲過程中需根據傳入參數動態構建表名、列名、WHERE 條件等無法用預處理佔位符（?）表示的 SQL 片段。

核心機制

1. CONCAT / CONCAT_WS 構建 SQL 字符串
2. 賦值給用戶變量 @sql
3. PREPARE stmt FROM @sql 解析準備
4. EXECUTE stmt 執行
5. DEALLOCATE PREPARE stmt 釋放

完整示例：通用 UPDATE 存儲過程

DELIMITER $$

CREATE PROCEDURE `sp_dynamic_update`(
  IN p_table VARCHAR(128),
  IN p_column VARCHAR(128),
  IN p_new_value VARCHAR(1000),
  IN p_where VARCHAR(4000)
)
BEGIN
  DECLARE v_sql VARCHAR(4000);
  
  -- 構建動態 SQL
  SET v_sql = CONCAT_WS(' ',
    'UPDATE', p_table,
    'SET', CONCAT(p_column, '=', QUOTE(p_new_value)),
    'WHERE', p_where
  );
  
  -- 準備並執行
  SET @sql = v_sql;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

調用示例

CALL sp_dynamic_update('users', 'status', '1', 'id = 100');
-- 等效於：UPDATE users SET status = '1' WHERE id = 100

關鍵技術點

1. SQL 注入防護

• 表名/列名無法用佔位符，**必須白名單驗證**
• 值類參數使用 QUOTE() 轉義或預處理佔位符

2. 用戶變量 @sql 作用域

會話級，存儲過程結束後仍存在，建議執行後 SET @sql = NULL; 清理。

3. PREPARE 限制

• 不支持某些語句（如 LOCK TABLES、PREPARE 本身）
• 每個會話最大預處理語句數受 max_prepared_stmt_count 限制（默認 16382）

進階模式：帶參數的動態查詢

CREATE PROCEDURE `sp_dynamic_select`(
  IN p_table VARCHAR(64),
  IN p_where VARCHAR(500),
  IN p_limit INT
)
BEGIN
  SET @sql = CONCAT('SELECT * FROM ', p_table, ' WHERE ', p_where, ' LIMIT ?');
  PREPARE stmt FROM @sql;
  SET @lim = p_limit;
  EXECUTE stmt USING @lim;
  DEALLOCATE PREPARE stmt;
END

關鍵：USING @lim 綁定佔位符參數，實現「動態結構 + 參數化值」雙重安全。

錯誤處理模板

BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS CONDITION 1
      @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT;
    SELECT CONCAT('Error ', @errno, ': ', @msg) AS error_info;
    DEALLOCATE PREPARE stmt; -- 確保釋放
  END;
  
  -- 動態 SQL 邏輯
  SET @sql = ...;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

性能考量

• 動態 SQL 無法利用查詢緩存（MySQL 8.0 已移除查詢緩存）
• 頻繁 PREPARE/EXECUTE 有解析開銷，熱點邏輯優先用靜態 SQL
• 複雜報表類、DDL 封裝、多租戶分表查詢適合動態 SQL

總結

MySQL 存儲過程動態 SQL 核心模式：構建字符串 → 用戶變量 → PREPARE → EXECUTE → DEALLOCATE。配合白名單驗證表/列名、QUOTE() 轉義值、參數化佔位符、異常處理釋放資源，即可安全靈活實現運行時 SQL 構建。