AWS RDS MySQL调用存储过程报错：子查询返回多行

让我直接帮你定位问题并解决！

错误原因分析 #

你遇到的Error Code: 1242. Subquery returns more than 1 row错误，根源出在调用存储过程的子查询里——你加了GROUP BY receiver_id，这会让查询结果返回多行单个ID值，而不是你期望的一个包含所有ID的逗号分隔字符串。存储过程的ReceiverIds参数只能接收单个值，自然就触发报错了。

修正后的调用语句 #

只需要去掉子查询里的GROUP BY receiver_id，GROUP_CONCAT本身就会把所有符合条件的receiver_id合并成一个逗号分隔的字符串：

SET group_concat_max_len = 2048;
call MyProcedure(
    'random_test_uuid',
    (
        SELECT CAST(GROUP_CONCAT(receiver_id SEPARATOR ',') AS CHAR) AS receiver_ids
        FROM receiver
        WHERE user_id LIKE (SELECT user_id FROM user WHERE user_name LIKE 'myName')
        -- 移除了这里的GROUP BY receiver_id
    ),
    '2017-09-24 23:44:32'
);

额外优化建议：提升存储过程的执行效率 #

你当前的存储过程用循环逐条插入数据，当ID数量较多时效率会很低。推荐用递归CTE拆分字符串+批量插入的方式改写，性能会好很多：

DELIMITER //
CREATE PROCEDURE MyProcedure(
    IN ItemUUID VARCHAR(255),
    IN ReceiverIds TEXT,
    IN ItemCreated VARCHAR(255)
)
BEGIN
    -- 提前处理空值，避免无效操作
    IF ReceiverIds IS NULL OR ReceiverIds = '' THEN
        RETURN;
    END IF;

    -- 用递归CTE拆分逗号分隔的ID串
    WITH RECURSIVE split_ids AS (
        SELECT 
            SUBSTRING_INDEX(ReceiverIds, ',', 1) AS receiver_id,
            SUBSTRING(ReceiverIds, LENGTH(SUBSTRING_INDEX(ReceiverIds, ',', 1)) + 2) AS remaining_ids
        UNION ALL
        SELECT 
            SUBSTRING_INDEX(remaining_ids, ',', 1) AS receiver_id,
            SUBSTRING(remaining_ids, LENGTH(SUBSTRING_INDEX(remaining_ids, ',', 1)) + 2) AS remaining_ids
        FROM split_ids
        WHERE remaining_ids != ''
    )
    -- 批量插入所有拆分后的ID
    INSERT INTO item_receiver (item_uuid, receiver_id, item_created)
    SELECT ItemUUID, receiver_id, ItemCreated
    FROM split_ids;
END//
DELIMITER ;

这种方式会一次性拆分所有ID，然后通过单条INSERT语句完成批量插入，比循环逐条插入的效率提升非常明显，尤其是当需要处理成百上千个ID的时候。

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