如何将字符串作为参数传入MySQL存储过程的IN子句

我有一个带参数的存储过程，单值调用完全正常：

CALL GetSelectedCodesCount('46.12', 'yes', 'uo');

但需要在第一个参数中传入多个值，比如：

CALL GetSelectedCodesCount(" '46.12' , '46.13' ", 'yes', 'uo');

这些参数要用于SQL的IN子句，我试过多种字符串处理方式：

SET selectedCodes = REPLACE(selectedCodes, ' ', '');
SET selectedCodes = CONCAT("'", REPLACE(selectedCodes, ",", "','"), "'");
SET selectedCodes = REPLACE(selectedCodes, '"', "' ");

处理后的字符串格式看起来符合IN语句要求，但传入多值时要么返回0要么返回null，只有单值传入时能正常返回结果。

以下是我的存储过程完整代码：

CREATE DEFINER=`***`@`%` PROCEDURE `database`.`GetSelectedCodesCount`(
    IN selectedCodes TEXT, 
    IN primaryCondition VARCHAR(50),
    IN tableName VARCHAR(50)
)
BEGIN
    DECLARE result INT;
    DECLARE result_uo INT;
    DECLARE result_fop INT;

    IF tableName = 'uo' THEN
        -- UO类型统计
        SELECT COUNT(*) INTO result_uo
        FROM table1 
        JOIN table2 ON table2.CODE = table1.CODE 
        JOIN table3 ON table3.RECORD = table2.RECORD 
        WHERE table1.CODE IN (selectedCodes) 
        AND table3.STAN = 'registered' 
        AND (primaryCondition IS NULL OR table2.PRIMARY = primaryCondition);

        SET result = result_uo;
    ELSEIF tableName = 'fop' THEN
        -- FOP类型统计
        SELECT COUNT(*) INTO result_fop
        FROM table1 
        JOIN table4 ON table4.CODE = table1.CODE 
        JOIN table5 ON table5.RECORD = table4.RECORD 
        WHERE table1.CODE IN (selectedCodes) 
        AND table5.STAN = 'registered' 
        AND (primaryCondition IS NULL OR table4.PRIMARY = primaryCondition);

        SET result = result_fop;
    ELSE
        -- 两种类型都统计
        -- UO统计
        SELECT COUNT(*) INTO result_uo
        FROM table1 
        JOIN table2 ON table2.CODE = table1.CODE 
        JOIN table3 ON table3.RECORD = table2.RECORD 
        WHERE table1.CODE IN (selectedCodes) 
        AND table3.STAN = 'registered' 
        AND (primaryCondition IS NULL OR table2.PRIMARY = primaryCondition);

        -- FOP统计
        SELECT COUNT(*) INTO result_fop
        FROM table1 
        JOIN table4 ON table4.CODE = table1.CODE 
        JOIN table5 ON table5.RECORD = table4.RECORD 
        WHERE table1.CODE IN (selectedCodes) 
        AND table5.STAN = 'registered' 
        AND (primaryCondition IS NULL OR table4.PRIMARY = primaryCondition);

        -- 结果求和
        SET result = result_uo + result_fop;
    END IF;

    SELECT result;
END

问题原因 #

核心问题是IN子句无法直接识别字符串参数中的多个值，数据库会把整个selectedCodes字符串当成单个匹配值，而不是自动拆分成多个值去匹配，所以多值传入时自然找不到匹配结果，返回0或null。

解决方法 #

方法1：使用动态SQL拼接执行 #

修改存储过程，将查询语句拼接成动态SQL后执行，让数据库解析IN子句中的多值：

CREATE DEFINER=`***`@`%` PROCEDURE `database`.`GetSelectedCodesCount`(
    IN selectedCodes TEXT, 
    IN primaryCondition VARCHAR(50),
    IN tableName VARCHAR(50)
)
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE sqlQuery TEXT;
    DECLARE whereCondition TEXT;

    -- 处理primaryCondition的过滤条件
    SET whereCondition = IF(primaryCondition IS NULL, '', CONCAT(' AND t.PRIMARY = ''', primaryCondition, ''''));

    IF tableName = 'uo' THEN
        SET sqlQuery = CONCAT(
            'SELECT COUNT(*) INTO @result_uo ',
            'FROM table1 ',
            'JOIN table2 t ON t.CODE = table1.CODE ',
            'JOIN table3 ON table3.RECORD = t.RECORD ',
            'WHERE table1.CODE IN (', selectedCodes, ') ',
            'AND table3.STAN = ''registered'' ',
            whereCondition
        );
    ELSEIF tableName = 'fop' THEN
        SET sqlQuery = CONCAT(
            'SELECT COUNT(*) INTO @result_fop ',
            'FROM table1 ',
            'JOIN table4 t ON t.CODE = table1.CODE ',
            'JOIN table5 ON table5.RECORD = t.RECORD ',
            'WHERE table1.CODE IN (', selectedCodes, ') ',
            'AND table5.STAN = ''registered'' ',
            whereCondition
        );
    ELSE
        -- 统计UO
        SET sqlQuery = CONCAT(
            'SELECT COUNT(*) INTO @result_uo ',
            'FROM table1 ',
            'JOIN table2 t ON t.CODE = table1.CODE ',
            'JOIN table3 ON table3.RECORD = t.RECORD ',
            'WHERE table1.CODE IN (', selectedCodes, ') ',
            'AND table3.STAN = ''registered'' ',
            whereCondition
        );
        PREPARE stmt FROM sqlQuery;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 统计FOP
        SET sqlQuery = CONCAT(
            'SELECT COUNT(*) INTO @result_fop ',
            'FROM table1 ',
            'JOIN table4 t ON t.CODE = table1.CODE ',
            'JOIN table5 ON table5.RECORD = t.RECORD ',
            'WHERE table1.CODE IN (', selectedCodes, ') ',
            'AND table5.STAN = ''registered'' ',
            whereCondition
        );
        PREPARE stmt FROM sqlQuery;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET result = @result_uo + @result_fop;
        SELECT result;
        LEAVE;
    END IF;

    -- 执行单类型查询并返回结果
    PREPARE stmt FROM sqlQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET result = IF(tableName = 'uo', @result_uo, @result_fop);
    SELECT result;
END

调用时直接传入逗号分隔的带引号值即可：

CALL GetSelectedCodesCount('46.12','46.13', 'yes', 'uo');
-- 或无空格格式：'46.12,46.13'

方法2：使用JSON数组+临时表（MySQL 8.0+适用） #

通过JSON数组传入多值，转换为临时表后用JOIN替代IN子句，避免动态SQL的注入风险：

-- 先创建临时表存储传入的编码
CREATE TEMPORARY TABLE IF NOT EXISTS temp_codes (code VARCHAR(20));

-- 修改存储过程接收JSON数组参数
CREATE DEFINER=`***`@`%` PROCEDURE `database`.`GetSelectedCodesCount`(
    IN selectedCodes JSON, 
    IN primaryCondition VARCHAR(50),
    IN tableName VARCHAR(50)
)
BEGIN
    DECLARE result INT DEFAULT 0;
    -- 清空临时表
    TRUNCATE TABLE temp_codes;
    -- 将JSON数组插入临时表
    INSERT INTO temp_codes(code)
    SELECT JSON_UNQUOTE(value) FROM JSON_TABLE(selectedCodes, '$[*]' COLUMNS(value VARCHAR(20) PATH '$')) AS jt;

    IF tableName = 'uo' THEN
        SELECT COUNT(*) INTO result
        FROM table1 
        JOIN temp_codes ON temp_codes.code = table1.CODE
        JOIN table2 ON table2.CODE = table1.CODE 
        JOIN table3 ON table3.RECORD = table2.RECORD 
        WHERE table3.STAN = 'registered' 
        AND (primaryCondition IS NULL OR table2.PRIMARY = primaryCondition);
    ELSEIF tableName = 'fop' THEN
        SELECT COUNT(*) INTO result
        FROM table1 
        JOIN temp_codes ON temp_codes.code = table1.CODE
        JOIN table4 ON table4.CODE = table1.CODE 
        JOIN table5 ON table5.RECORD = table4.RECORD 
        WHERE table5.STAN = 'registered' 
        AND (primaryCondition IS NULL OR table4.PRIMARY = primaryCondition);
    ELSE
        SELECT COUNT(*) INTO result
        FROM (
            SELECT table1.CODE FROM table1 
            JOIN temp_codes ON temp_codes.code = table1.CODE
            JOIN table2 ON table2.CODE = table1.CODE 
            JOIN table3 ON table3.RECORD = table2.RECORD 
            WHERE table3.STAN = 'registered' 
            AND (primaryCondition IS NULL OR table2.PRIMARY = primaryCondition)
            UNION ALL
            SELECT table1.CODE FROM table1 
            JOIN temp_codes ON temp_codes.code = table1.CODE
            JOIN table4 ON table4.CODE = table1.CODE 
            JOIN table5 ON table5.RECORD = table4.RECORD 
            WHERE table5.STAN = 'registered' 
            AND (primaryCondition IS NULL OR table4.PRIMARY = primaryCondition)
        ) AS combined;
    END IF;

    SELECT result;
    DROP TABLE temp_codes;
END

调用时传入JSON数组：

CALL GetSelectedCodesCount('["46.12","46.13"]', 'yes', 'uo');

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