MySQL 動態 SQL 預存程序實戰：PREPARE/EXECUTE 靈活構建查詢與插入

動態 SQL 適用場景

• WHERE 條件、ORDER BY 排序欄位由應用層動態傳入。
• 表名、欄位名需參數化 (無法用佔位符，須字串拼接)。
• 複雜報表邏輯，靜態 SQL 難以覆蓋所有分支。

核心機制：PREPARE / EXECUTE / DEALLOCATE PREPARE

SET @sql = 'SELECT * FROM t WHERE id = ?';
PREPARE stmt FROM @sql;
SET @id = 100;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

實戰範例 1：動態條件查詢用戶

DELIMITER $$
CREATE PROCEDURE GetUsersDynamic(
    IN where_cond VARCHAR(500),   -- 如 'age <= 30 AND status=1'
    IN order_by VARCHAR(100)      -- 如 'age DESC' 或 ''
)
BEGIN
    DECLARE stmt_text VARCHAR(2000);
    
    IF LENGTH(TRIM(order_by)) > 0 THEN
        SET @sql = CONCAT('SELECT id, name, password, age, DATE_FORMAT(add_date, "%Y-%m-%d") AS AddDate 
                           FROM users WHERE ', where_cond, ' ORDER BY ', order_by);
    ELSE
        SET @sql = CONCAT('SELECT id, name, password, age, DATE_FORMAT(add_date, "%Y-%m-%d") AS AddDate 
                           FROM users WHERE ', where_cond);
    END IF;
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- 呼叫
CALL GetUsersDynamic('age <= 30', 'age DESC');
CALL GetUsersDynamic('status = 1', '');

實戰範例 2：動態插入 (單引號轉義技巧)

DELIMITER $$
CREATE PROCEDURE InsertUser(
    IN p_name VARCHAR(50),
    IN p_pass VARCHAR(32),
    IN p_age INT,
    IN p_add_date DATETIME
)
BEGIN
    DECLARE stmt_text VARCHAR(2000);
    -- 四個單引號代表字串內的一個單引號: '' -> '
    SET @sql = CONCAT('INSERT INTO users (name, password, age, add_date) VALUES (',
                      QUOTE(p_name), ',',  -- QUOTE() 自動處理單引號轉義並加引號，最安全
                      QUOTE(p_pass), ',',
                      p_age, ',',
                      QUOTE(p_add_date), ')');
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

安全提示：動態 SQL 風險極高，嚴禁直接拼接用戶輸入。務必使用 QUOTE() 函數或應用層參數化，防止 SQL 注入。

輔助函數：日期格式化

CREATE FUNCTION get_date_str(dt DATETIME) RETURNS VARCHAR(50)
DETERMINISTIC
RETURN DATE_FORMAT(dt, '%Y-%m-%d');

效能與限制

• 執行計畫不快取：每次 PREPARE 重新解析優化，高併發下 CPU 開銷大。
• 無法使用查詢快取 (MySQL 5.7 已移除 Query Cache)。
• 權限檢查：執行者需擁有動態 SQL 訪問的底層表權限。
• 調試困難：錯誤訊息不顯示展開後的完整 SQL，建議開發期 SELECT @sql; 列印除錯。

現代替代方案

1. 應用層構建 SQL：ORM/Query Builder 產生參數化 SQL，由連線池發送，效能最佳。
2. 條件下推：將所有可能條件寫在靜態 SQL，用 WHERE (cond IS NULL OR col = cond) 模式，優化器可裁剪無效條件。
3. JSON 欄位查詢：MySQL 5.7+ JSON_EXTRACT、->> 運算子，半結構化查詢無需動態 SQL。

總結

動態 SQL 是「核武級」工具：靈活但危險。生產環境**能不用盡量不用**；必須用時，嚴格驗證輸入、使用 QUOTE()、限制呼叫頻率、完善審計日誌。優先考慮應用層構建或靜態 SQL 條件下推模式。