MySQL存储过程传入多ID遇Truncated incorrect DOUBLE value错误求解

这个问题我太熟了！你遇到的Truncated incorrect DOUBLE value错误，根源在于MySQL处理IN子句的方式——当你把'1,2,3'这样的字符串传给IN(list_of_ids)时，MySQL不会自动把它拆分成多个独立的ID，而是把整个字符串当成一个单一值，试图把它转换成和id匹配的数字类型，这就导致了类型转换失败。单个ID能正常运行，是因为单个数字字符串可以直接转成数字，不会触发截断错误。

下面给你几种靠谱的解决方案，按需选择：

动态SQL的思路是把传入的ID列表直接拼接到查询语句里，让MySQL执行时能正确识别多个ID。示例存储过程如下：

DELIMITER //
CREATE PROCEDURE get_data_by_ids(IN list_of_ids VARCHAR(100))
BEGIN
    -- 拼接动态SQL语句
    SET @sql_query = CONCAT('SELECT * FROM `table` WHERE id IN (', list_of_ids, ')');
    -- 预处理并执行语句
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    -- 释放预处理语句
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

⚠️ 注意：如果list_of_ids的来源是不可信的用户输入，这种方法可能存在SQL注入风险，一定要先对输入做校验（比如确保只包含数字和逗号）。

如果担心SQL注入，可以先创建一个字符串拆分函数，把传入的ID列表拆分成单独的行，再用子查询关联。

步骤1：创建拆分函数（适用于MySQL 5.x及以上） #

DELIMITER //
CREATE FUNCTION split_id_list(
    input_str VARCHAR(100),
    delimiter_char VARCHAR(1),
    position INT
) RETURNS INT
DETERMINISTIC
BEGIN
    RETURN CAST(REPLACE(SUBSTRING(SUBSTRING_INDEX(input_str, delimiter_char, position),
        LENGTH(SUBSTRING_INDEX(input_str, delimiter_char, position - 1)) + 1),
        delimiter_char, '') AS UNSIGNED);
END //
DELIMITER ;

步骤2：修改存储过程，用拆分函数查询 #

如果是MySQL 8.0+，可以用递归CTE生成数字序列来遍历拆分每个ID：

DELIMITER //
CREATE PROCEDURE get_data_by_ids(IN list_of_ids VARCHAR(100))
BEGIN
    WITH RECURSIVE id_positions AS (
        SELECT 1 AS pos
        UNION ALL
        SELECT pos + 1 FROM id_positions 
        WHERE pos <= (LENGTH(list_of_ids) - LENGTH(REPLACE(list_of_ids, ',', '')) + 1)
    )
    SELECT t.* 
    FROM `table` t
    JOIN (
        SELECT split_id_list(list_of_ids, ',', pos) AS id 
        FROM id_positions
    ) ids ON t.id = ids.id;
END //
DELIMITER ;

方法三：应用层传递参数化列表（如果可控） #

如果是从应用程序调用存储过程，有些驱动支持传递数组参数（比如JDBC的Array类型），不过MySQL本身的存储过程不直接支持数组类型，需要驱动帮你处理成多个参数或者拆分后的查询。不过这种方法依赖应用层的实现，灵活性稍差。

总结一下：如果只是内部使用、能保证输入安全，动态SQL是最快的方案；如果需要更高的安全性，字符串拆分函数更稳妥。MySQL没有list=true这类设置，得通过上面的方法来实现多ID传入的需求。

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