MySQL存储过程无法返回预期结果，请求排查修复

我帮你检查了这段代码，发现好几个关键问题导致你拿到空表，咱们一个个来理清楚并修复：

1. 变量引用错误：输入参数与会话变量混淆 #

你定义的存储过程输入参数是variants（不带@），但调用split_string函数时传的是未初始化的会话变量@variants：

SET @variant = split_string(@variants, ';', counter);

这会让函数拿到NULL值，返回的-1永远不满足@variant > 0的判断，最终@correct_params里只有初始的错误元素，导致查询返回空结果。

修复： 把@variants替换为输入参数variants：

SET @variant = split_string(variants, ';', counter);

2. 初始JSON数组初始化错误 #

你现在的初始化代码把一个字符串当成数组元素存入：

SET @correct_params = JSON_ARRAY('[3, 1]');

这会让数组第一个元素是字符串"[3, 1]"，而非数字3和1。后续用JSON_TABLE解析时，这个字符串无法转成INT，要么变成NULL，要么直接导致解析失败。

修复： 如果你需要初始包含3和1，直接初始化数组为两个独立数字：

SET @correct_params = JSON_ARRAY(3, 1);

如果不需要初始元素，换成空数组JSON_ARRAY()即可。

3. 分隔符不匹配：存储过程与测试调用的分隔符不一致 #

你的存储过程是按;来拆分字符串的：

@params_number = (CHAR_LENGTH(variants) - CHAR_LENGTH(REPLACE(variants, ';', '')) + 1)

但测试时你传的是逗号分隔的字符串：

CALL new_procedure('2, b7, vb, 9');

这会导致@params_number被计算为1（因为没有找到;），循环只执行一次，把整个字符串传给函数后，因不是纯数字被过滤，最终没有新元素加入数组。

修复： 要么统一用;分隔测试参数：

CALL new_procedure('2; b7; vb; 9');

要么把存储过程里的分隔符改成逗号，根据你的实际需求调整。

4. 函数参数与变量的优化问题 #

split_string函数里用了MySQL关键字delimimiter作为参数名，容易引发语法冲突；同时使用会话变量@splitted_string、@splitted_number可能污染全局会话环境。

修复后的函数：

CREATE DEFINER=`root`@`localhost` FUNCTION `split_string`(
    string_to_split VARCHAR(250),
    delimiter_str VARCHAR(5),
    position INT
) RETURNS int
    DETERMINISTIC
BEGIN
    DECLARE splitted_string VARCHAR(250);
    DECLARE splitted_number INT;
    SET splitted_string = REPLACE(
        SUBSTRING(
            SUBSTRING_INDEX(string_to_split, delimiter_str, position),
            CHAR_LENGTH(SUBSTRING_INDEX(string_to_split, delimiter_str, position - 1)) + 1
        ), delimiter_str, ''
    );
    SET splitted_number = (
        CASE WHEN splitted_string REGEXP '^[0-9]+$' THEN
            CAST(splitted_string AS UNSIGNED) ELSE -1 END
        );
    RETURN splitted_number;
END

5. 冗余逻辑清理 #

你代码里的@validated_params变量和相关判断完全没有被使用，属于冗余代码，可以直接删除，简化存储过程逻辑。

修复后的完整存储过程 #

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(
    IN variants VARCHAR(50)
)
BEGIN
    DECLARE counter INT DEFAULT 0;
    -- 初始化带预期初始元素的数组
    SET @correct_params = JSON_ARRAY(3, 1);
    -- 计算拆分后的元素数量（注意分隔符要与调用时一致）
    SET @params_number = (CHAR_LENGTH(variants) - CHAR_LENGTH(REPLACE(variants, ';', '')) + 1);

    WHILE counter < @params_number DO 
        SET counter = counter + 1;
        -- 引用正确的输入参数
        SET @variant = split_string(variants, ';', counter);
        IF @variant > 0 THEN
            SET @correct_params = JSON_ARRAY_APPEND(@correct_params, '$', @variant);
        END IF;
    END WHILE;

    -- 解析JSON数组为单列结果
    SELECT variant_number 
    FROM JSON_TABLE(@correct_params, '$[*]' COLUMNS (variant_number INT PATH '$')) AS vt;
END

测试验证 #

执行以下调用（用;分隔参数）：

CALL new_procedure('2; b7; vb; 9');

会返回你预期的结果：

备注：内容来源于stack exchange，提问作者Sparkle