MySQL CONCAT 拼接數字變字符串問題：預處理語句動態 SQL 完美解決

問題現象

使用 `CONCAT` 拼接 SQL 時，數字類型參數被隱式轉為字符串，導致 `UPDATE` 語句中數值列接收字符串，可能觸發隱式轉換、索引失效或報錯。

原始問題代碼

-- 動態構建 SQL，paramas 為列名，vals 為值
SET @sql = CONCAT('UPDATE t_scheme_info SET ', paramas, ' = ? WHERE id = ?');
-- 若 paramas 為數值類型列，vals 通過 CONCAT 傳入可能被視為字符串

解決方案：預處理語句 + 用戶變量（已驗證有效）

DELIMITER //

DROP PROCEDURE IF EXISTS save_scheme_params;
CREATE PROCEDURE save_scheme_params(
    IN xid INT UNSIGNED,      -- 記錄 ID
    IN nums INT,              -- 表標識: 1=info, 其他=item
    IN paramas VARCHAR(255),  -- 列名
    IN vals VARCHAR(255)      -- 值(作為字符串傳入)
)
BEGIN
    -- 1. 動態構建 SQL 模板，佔位符 ?
    IF nums = 1 THEN 
        SET @sql = CONCAT('UPDATE t_scheme_info SET ', paramas, ' = ? WHERE id = ?');
    ELSE
        SET @sql = CONCAT('UPDATE t_scheme_item SET ', paramas, ' = ? WHERE id = ?');
    END IF;
    
    -- 2. 準備預處理語句
    PREPARE stmt FROM @sql;
    
    -- 3. 設置用戶變量綁定參數
    -- 關鍵：MySQL 會根據列類型自動將字符串轉換為目標類型(數值/日期等)
    SET @vals = vals;
    SET @id = xid;
    
    -- 4. 執行，參數類型由數據庫引擎根據表結構推斷轉換
    EXECUTE stmt USING @vals, @id;
    
    -- 5. 釋放預處理語句
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

-- 調用示例
CALL save_scheme_params(1, 1, 'lease_model_info', '測試中');
CALL save_scheme_params(100, 2, 'amount', '12345.67'); -- 數值列自動轉換

核心原理解析

1. 預處理語句參數綁定 (`USING @vals`)：MySQL 服務器根據**目標列的數據類型**，自動將傳入的字符串值轉換為對應類型（INT、DECIMAL、DATE 等），避免了客戶端手動轉換或隱式轉換風險。
2. 列名動態拼接 (`CONCAT ... paramas`)：列名無法作為預處理參數，只能字符串拼接。**必須嚴格驗證 `paramas` 合法性**（白名單），防止 SQL 注入。
3. 用戶變量 (`@vals`, `@id`)：作為 `EXECUTE ... USING` 的參數載體，類型靈活。

安全增強：列名白名單驗證

-- 在存儲過程內部驗證
IF paramas NOT IN ('lease_model_info', 'amount', 'status', 'create_time') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '非法列名';
END IF;

應用層最佳實踐（Java/JDBC 示例）

// 避免在應用層拼接 SQL，直接調用存儲過程
String sql = "{CALL save_scheme_params(?, ?, ?, ?)}";
try (CallableStatement cs = conn.prepareCall(sql)) {
    cs.setInt(1, id);
    cs.setInt(2, tableFlag);
    cs.setString(3, columnName); // 已白名單驗證
    cs.setString(4, value);      // 統一傳字符串，交由 DB 轉換
    cs.executeUpdate();
}

總結

MySQL `CONCAT` 拼接導致類型喪失，**預處理語句 + 用戶變量綁定** 利用服務端類型系統自動轉換，是動態列更新的標準、安全、高效方案。配合**列名白名單**，徹底解決類型與安全雙重問題。