MySQL预编译存储过程返回错误结果问题求助

表结构 #

CREATE TABLE IF NOT EXISTS InsuranceContract (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`enquiryCode` VARCHAR(20) DEFAULT NULL,
`contractCode` VARCHAR(20) DEFAULT NULL,
`createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP (),
`updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP () ON UPDATE CURRENT_TIMESTAMP (),
UNIQUE KEY (`enquiryCode`)) ENGINE=INNODB DEFAULT CHARSET=UTF8 COLLATE = UTF8_BIN;

问题存储过程 #

DROP procedure IF EXISTS `sp_insurance_contract_get`;
DELIMITER $$ 
CREATE PROCEDURE `sp_insurance_contract_get` (enquiryCode VARCHAR(20), contractCode VARCHAR(20)) 
BEGIN
    SET @t1 = "SELECT * FROM InsuranceContract 
               WHERE InsuranceContract.enquiryCode = enquiryCode 
                     AND InsuranceContract.contractCode = contractCode;";
    PREPARE param_stmt FROM @t1;
  EXECUTE param_stmt;
  DEALLOCATE PREPARE param_stmt;
END$$ 
DELIMITER ;

执行语句 #

CALL sp_insurance_contract_get('EQ000000000014', '3001002');

预期返回1行数据，但实际返回表中所有记录，直接执行对应SQL语句结果正确。

错误原因 #

存储过程的参数名与表字段名完全相同（enquiryCode、contractCode），在动态SQL字符串中，MySQL会将这些名称解析为表的字段名，而非存储过程的参数值。这导致WHERE条件等价于：

WHERE InsuranceContract.enquiryCode = InsuranceContract.enquiryCode 
  AND InsuranceContract.contractCode = InsuranceContract.contractCode

上述条件对表中所有行都成立，因此返回了全部记录。

解决方案 #

方案1：使用占位符传递参数（推荐，避免SQL注入） #

通过?作为占位符，在EXECUTE时用USING传递参数，无需担心变量名冲突：

DROP procedure IF EXISTS `sp_insurance_contract_get`;
DELIMITER $$ 
CREATE PROCEDURE `sp_insurance_contract_get` (enquiryCode VARCHAR(20), contractCode VARCHAR(20)) 
BEGIN
    SET @t1 = "SELECT * FROM InsuranceContract 
               WHERE InsuranceContract.enquiryCode = ? 
                     AND InsuranceContract.contractCode = ?;";
    PREPARE param_stmt FROM @t1;
    EXECUTE param_stmt USING enquiryCode, contractCode;
    DEALLOCATE PREPARE param_stmt;
END$$ 
DELIMITER ;

方案2：修改参数名避免冲突 #

给存储过程参数添加前缀（如p_），明确区分参数与字段，再拼接参数到动态SQL中：

DROP procedure IF EXISTS `sp_insurance_contract_get`;
DELIMITER $$ 
CREATE PROCEDURE `sp_insurance_contract_get` (p_enquiryCode VARCHAR(20), p_contractCode VARCHAR(20)) 
BEGIN
    SET @t1 = CONCAT("SELECT * FROM InsuranceContract 
               WHERE InsuranceContract.enquiryCode = '", p_enquiryCode, "' 
                     AND InsuranceContract.contractCode = '", p_contractCode, "';");
    PREPARE param_stmt FROM @t1;
    EXECUTE param_stmt;
    DEALLOCATE PREPARE param_stmt;
END$$ 
DELIMITER ;

注意：方案2存在SQL注入风险，若参数来自不可信输入，优先使用方案1。

内容的提问来源于stack exchange，提问作者Dang Kien