MySQL 存儲過程動態建表與執行：PREPARE/EXECUTE/DEALLOCATE PREPARE 實戰

動態 SQL 核心三步驟

PREPARE stmt_name FROM sql_text;      -- 1. 準備/解析/編譯 SQL 模板
EXECUTE stmt_name [USING @var1, ...]; -- 2. 執行，可傳參數 (用戶變量 @var)
DEALLOCATE PREPARE stmt_name;         -- 3. 釋放準備語句資源

實戰案例：動態表名查詢最新 GPS 數據

場景：表名由參數決定，需動態構建 SQL 查詢指定設備最新軌跡點。

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_test`$$
CREATE PROCEDURE `sp_test`(
    IN gpstime_ VARCHAR(128),
    IN gpsname_ VARCHAR(128),
    IN gpsinfo_ VARCHAR(256)
)
BEGIN
    DECLARE tbname VARCHAR(50) DEFAULT 'gps_data';  -- 假設表名邏輯
    DECLARE v_sql VARCHAR(1024) DEFAULT '';

    -- 構建動態 SQL (注意字符串拼接與引號嵌套)
    SET v_sql = CONCAT('SELECT * FROM `', tbname, '` WHERE gpsname = ? ORDER BY gpstime DESC LIMIT 1');

    -- 使用用戶變量 @v_sql 存儲 SQL 文本 (PREPARE 要求)
    SET @v_sql = v_sql;

    -- 1. PREPARE
    PREPARE stmt_dynamic FROM @v_sql;

    -- 2. EXECUTE (使用 USING 傳參，防止 SQL 注入，併發安全)
    SET @gpsname_param = gpsname_;
    EXECUTE stmt_dynamic USING @gpsname_param;

    -- 3. DEALLOCATE (必須釋放，否則會話結束前佔用 prepared_statement 配額)
    DEALLOCATE PREPARE stmt_dynamic;

    -- 調試輸出
    SELECT v_sql AS generated_sql;
END$$
DELIMITER ;

關鍵技術點深度解析

1. 用戶變量 (@var) vs 局部變量 (DECLARE var)

• PREPARE 和 EXECUTE ... USING **只能使用用戶變量 (@var)**，不能用局部變量
• 用戶變量：會話級、類型動態、SET @x = 1
• 局部變量：塊級、靜態類型、DECLARE x INT

2. 防止 SQL 注入：參數化執行 (USING)

錯誤示範：SET @sql = CONCAT('SELECT * FROM t WHERE name = ''', param, ''''); → 拼接注入風險
正確示範：SET @sql = 'SELECT * FROM t WHERE name = ?'; PREPARE s FROM @sql; EXECUTE s USING @param; → 參數化，安全高效

3. 動態 DDL (建表/加索引) 同理

SET @ddl = CONCAT('CREATE TABLE `', @tbl, '` (id INT PRIMARY KEY, data JSON) ENGINE=InnoDB');
PREPARE stmt_ddl FROM @ddl;
EXECUTE stmt_ddl;
DEALLOCATE PREPARE stmt_ddl;

**注意**：DDL 隱式提交事務，存儲過程中使用需謹慎設計事務邊界。

4. 資源洩漏防護

• 每個 PREPARE 必須配對 DEALLOCATE PREPARE
• 異常退出時也要釋放：可用 DECLARE EXIT HANDLER FOR SQLEXCEPTION DEALLOCATE PREPARE stmt_name; 兜底
• 會話級限制：max_prepared_stmt_count (默認 16382)，洩漏會導致後續 PREPARE 失敗

5. 性能優勢：解析緩存復用

同一會話重複執行相同結構 SQL 時，PREPARE 避免重複解析/優化，減少 CPU 開銷。配合 COM_STMT_EXECUTE 協議，網絡傳輸更精簡 (二進制參數)。

調用示例

CALL sp_test('2024-01-01 12:00:00', 'device_001', '{"speed": 60}');