MySQL PREPARE 預處理語句完整指南：從 SQL 介面到驅動層最佳實踐

MySQL PREPARE 預處理語句完整指南：從 SQL 介面到驅動層最佳實踐

預處理語句（Prepared Statement）通過「解析一次、多次執行」機制，顯著降低 SQL 注入風險與解析開銷。本文涵蓋 SQL 層介面（PREPARE/EXECUTE/DEALLOCATE PREPARE）、參數標記規則、會話作用域、支持語句範圍，並對比 C API/JDBC/PDO 等驅動層調用差異。

核心三句話

PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
{DEALLOCATE | DROP} PREPARE stmt_name;

關鍵規則速查

• stmt_name：不區分大小寫，會話級私有，斷開連接自動釋放。
• preparable_stmt：單條 SQL 文本或用戶變量，不支持多語句。
• ? 佔位符僅用於數據值，不可用於標識符（表名、列名）、關鍵字、LIMIT 參數（5.0.7+ 支持）等。
• USING 子句只接受用戶變量（@var），數量必須與 ? 一致。
• 同名 PREPARE 會隱式 DEALLOCATE 舊語句；若新語句語法錯誤，舊語句也會消失。

實戰範例

1. 字面量文本預處理

PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @a = 3; SET @b = 4;
EXECUTE stmt1 USING @a, @b;  -- 返回 5
DEALLOCATE PREPARE stmt1;

2. 用戶變量承載 SQL 文本

SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
PREPARE stmt2 FROM @s;
SET @a = 6; SET @b = 8;
EXECUTE stmt2 USING @a, @b;  -- 返回 10
DEALLOCATE PREPARE stmt2;

3. LIMIT 動態參數化（5.0.7+）

SET @skip = 1; SET @num = 5;
PREPARE stmt3 FROM 'SELECT * FROM t1 LIMIT ?, ?';
EXECUTE stmt3 USING @skip, @num;  -- 返回第 2-6 行
DEALLOCATE PREPARE stmt3;

驅動層調用對比

限制與避坑

1. 不支持的語句： LOCK TABLES、CHECK TABLE、大多數 DDL（CREATE DATABASE 除外）、存儲函數/觸發器內部不可用。
2. 嵌套禁止： PREPARE 文本不能是另一個 PREPARE/EXECUTE/DEALLOCATE。
3. 會話隔離： A 連接創建的預處理語句，B 連接不可見。
4. 文本協議性能： SQL 介面（PREPARE）走文本協議，不如驅動層二進制協議高效，僅用於測試、無驅動環境或動態 SQL 構建場景。

最佳實踐清單

• 應用層優先使用驅動層預處理 API，而非 SQL 介面。
• 長連接池中顯式 DEALLOCATE 或依賴連接關閉自動清理，避免洩漏。
• 動態構建 IN (?,?,...) 列表時，預先生成佔位符字串再 PREPARE。
• 監控 Prepared_stmt_count 狀態變量，防止預處理語句數量過大佔用內存。

預處理是防注入、提性能的雙重保障，建議將其作為團隊 SQL 開發規範的強制項，並通過 Code Review 與靜態分析工具落地執行。