带IN参数的MySQL动态查询存储过程参数替换异常问题

问题现状 #

你编写的存储过程SP_TEST_1在生成动态查询时，未能将p_search参数的实际值（如peter）代入SQL语句，而是直接输出了字符串%@p_search%，导致查询无法匹配目标数据。

解决方案 #

方法1：直接拼接参数值到动态SQL（适用于需要生成带实际值的SQL语句场景） #

修改存储过程中拼接LIKE条件的代码，将p_search参数与通配符%直接拼接，同时正确转义单引号：

USE test;

DELIMITER $$
CREATE PROCEDURE `SP_TEST_1`(
    IN `p_criteria` VARCHAR(50),
    IN `p_search` VARCHAR(50)
)
BEGIN
    DECLARE v_Query VARCHAR(4000); 
    SET v_Query = '';
    -- 原代码中CONCAT的两个单引号是转义写法，若需拼接空格可改为' '
    SET v_Query = 'SELECT CONCAT(u.firstname, '''', u.lastname) as group_name FROM users u';
    
    IF (p_criteria IS NOT NULL AND p_criteria = 'name') THEN
        SET v_Query = CONCAT(v_Query, ' WHERE LOWER(u.firstname)');
        -- 修正参数拼接逻辑：将p_search与通配符拼接，转义单引号
        SET v_Query = CONCAT(v_Query, ' LIKE LOWER(''%', p_search, '%'')');
    END IF;
    
    INSERT INTO testsql(qry) VALUES(CONCAT('q=', v_Query));             
    
    COMMIT;
    SET @result_set = v_Query;
    PREPARE statement FROM @result_set;
    EXECUTE statement;
    DEALLOCATE PREPARE statement;
END$$
DELIMITER ;

方法2：使用预编译参数传递（更安全，避免SQL注入） #

如果不需要在生成的SQL语句中直接显示实际参数值，推荐使用预编译占位符，通过USING子句传递参数，这是生产环境更安全的做法：

USE test;

DELIMITER $$
CREATE PROCEDURE `SP_TEST_1`(
    IN `p_criteria` VARCHAR(50),
    IN `p_search` VARCHAR(50)
)
BEGIN
    DECLARE v_Query VARCHAR(4000); 
    SET v_Query = '';
    SET v_Query = 'SELECT CONCAT(u.firstname, '''', u.lastname) as group_name FROM users u';
    
    IF (p_criteria IS NOT NULL AND p_criteria = 'name') THEN
        SET v_Query = CONCAT(v_Query, ' WHERE LOWER(u.firstname) LIKE LOWER(CONCAT(''%'', ?, ''%''))');
    END IF;
    
    INSERT INTO testsql(qry) VALUES(CONCAT('q=', v_Query));             
    
    COMMIT;
    SET @result_set = v_Query;
    PREPARE statement FROM @result_set;
    -- 通过USING传递参数
    IF (p_criteria IS NOT NULL AND p_criteria = 'name') THEN
        EXECUTE statement USING p_search;
    ELSE
        EXECUTE statement;
    END IF;
    DEALLOCATE PREPARE statement;
END$$
DELIMITER ;

说明 #

• 方法1会直接将参数值拼接到生成的SQL语句中，满足你需要看到实际值的需求，但要注意参数中含特殊字符（如单引号）可能导致SQL语法错误，需额外处理。
• 方法2使用预编译占位符，避免了SQL注入风险，同时参数传递更安全，适合大多数生产场景。

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