核心語法（MySQL 5.0+）
PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
{DEALLOCATE | DROP} PREPARE stmt_name;
基本範例
-- 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 = 10; SET @limit = 20;
PREPARE stmt3 FROM 'SELECT * FROM logs ORDER BY id DESC LIMIT ?, ?';
EXECUTE stmt3 USING @skip, @limit;
關鍵規則
| 規則 | 說明 |
|---|
| stmt_name 不分大小寫 | stmt1 = STMT1 |
| 佔位符 ? 無需引號 | 即使是字串參數 |
| 重複 PREPARE 同名稱 | 舊語句自動釋放，即使新語句報錯 |
| 作用域 | 當前 Session 可見，斷開自動釋放 |
| 支援語句類型 | SELECT, INSERT, UPDATE, DELETE, REPLACE, CREATE, SET, DO, 部分 SHOW |
| 不支援場景 | 存儲函數（Function）、Triggers（5.0.13+ 支援 Stored Procedure） |
儲存程序中動態 SQL 範例
CREATE PROCEDURE `search_users`(
IN p_id INT UNSIGNED,
IN p_name VARCHAR(50)
)
BEGIN
DECLARE sql_cmd TEXT DEFAULT 'SELECT * FROM users WHERE 1=1';
IF p_id IS NOT NULL THEN
SET sql_cmd = CONCAT(sql_cmd, ' AND id = ?');
PREPARE stmt FROM sql_cmd;
SET @a = p_id;
EXECUTE stmt USING @a;
DEALLOCATE PREPARE stmt;
ELSEIF p_name IS NOT NULL THEN
SET sql_cmd = CONCAT(sql_cmd, ' AND name LIKE ?');
PREPARE stmt FROM sql_cmd;
SET @a = CONCAT(p_name, '%');
EXECUTE stmt USING @a;
DEALLOCATE PREPARE stmt;
ELSE
PREPARE stmt FROM sql_cmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END;
防注入原理
- SQL 結構在
PREPARE 階段固化，參數在 EXECUTE 階段以二進位協議傳輸，徹底分離代碼與資料。
- 等同於 JDBC
PreparedStatement、PDO prepare() 的底層實現。
效能優勢
- 重複執行同結構 SQL：解析、優化、計畫生成僅一次。
- 減少網路傳輸（二進位協議 vs 文本 SQL）。
對比 SQL Server sp_executesql
| 特性 | MySQL PREPARE | SQL Server sp_executesql |
|---|
| 參數化 | ? 佔位符 + USING 變數 | @param 定義 + 輸出參數 |
| 計畫快取 | Session 級（連線池可複用） | Plan Cache 全域共享 |
| 動態 DDL | 支援 | 支援 |
| 輸出參數 | 不支援（用 SELECT 代替） | 支援 OUTPUT |
最佳實踐
- 應用層優先使用驅動提供的 Prepared Statement（PDO::prepare、mysqli::prepare、JDBC PreparedStatement）。
- 僅在儲存程序、動態建表、跨 Schema 操作等無法用應用層參數化時，使用 SQL 層 PREPARE。
- 嚴格
DEALLOCATE PREPARE，避免洩漏佔用 max_prepared_stmt_count（預設 16,382）。
掌握 PREPARE/EXECUTE，是編寫安全、高效動態 SQL 的核心技能。
