MySQL 存儲過程動態 SQL 解決變量名與列名衝突：INTE 信息表更新失敗案例

錯誤現場

update sdw_bus_inteinfo set INTE=INTE+integ, ... where U_ID=u_id;

問題：u_id 是存儲過程參數/變量，但 MySQL 解析器以為是列名 U_ID，導致 where U_ID = U_ID 恆真，全表更新。

根因：標識符解析優先級

MySQL 解析順序：列名 → 變量名 → 參數名。當列名與變量名同名時，列名優先。

解決方案：動態 SQL + 預處理語句

DECLARE my_sql VARCHAR(500);
SET my_sql = CONCAT('UPDATE sdw_bus_inteinfo SET INTE=INTE+', integ,
                    ', TOTAL_TOPUP=TOTAL_TOPUP+', money,
                    ', TOPUP=TOPUP+', integ,
                    ' WHERE U_ID=', QUOTE(u_id)); -- QUOTE 自動加引號並轉義
PREPARE stmt FROM my_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

完善版：參數化預處理（防注入、計劃複用）

SET @sql = 'UPDATE sdw_bus_inteinfo SET INTE=INTE+?, TOTAL_TOPUP=TOTAL_TOPUP+?, TOPUP=TOPUP+? WHERE U_ID=?';
PREPARE stmt FROM @sql;
SET @p1 = integ, @p2 = money, @p3 = integ, @p4 = u_id;
EXECUTE stmt USING @p1, @p2, @p3, @p4;
DEALLOCATE PREPARE stmt;

其他同名衝突場景與命名規範

完整存儲過程模板（含事務、異常處理、動態 SQL）

CREATE PROCEDURE sp_recharge(
    IN p_accid VARCHAR(32), IN p_money DECIMAL(15,3), ...
)
BEGIN
    DECLARE lv_result CHAR(1) DEFAULT '0';
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET lv_result = '1';
    
    START TRANSACTION;
    
    -- 靜態 SQL 正常執行
    UPDATE sdw_bus_account SET ACC_BLA=ACC_BLA+p_money WHERE ACC_ID=p_accid;
    
    -- 動態 SQL 解決同名衝突
    SET @sql = 'UPDATE sdw_bus_inteinfo SET INTE=INTE+? WHERE U_ID=?';
    PREPARE stmt FROM @sql;
    SET @integ = p_integ, @uid = p_u_id;
    EXECUTE stmt USING @integ, @uid;
    DEALLOCATE PREPARE stmt;
    
    IF lv_result <> '0' THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
    
    SELECT lv_result AS result;
END

調試技巧：SELECT 語句驗證動態 SQL

SELECT my_sql; -- 執行前打印生成的 SQL，肉眼檢查
-- 或寫入日志表
INSERT INTO debug_log(sql_text) VALUES (my_sql);