MySQL多值子查询执行UPDATE操作时Subquery returns more than 1 row报错的解决方法及循环更新实现问询

你遇到的问题很典型：当WHERE子句里用=匹配子查询结果时，子查询返回多行就会触发这个错误。其实不用手动写循环，SQL本身是集合型语言，我们可以用JOIN来一次性处理所有匹配的行，效率比循环高得多。

推荐方案：用JOIN替代子查询 #

把你的UPDATE语句改成JOIN关联的形式，这样可以同时匹配所有从combo_estoque返回的estoques_id，一次性完成所有更新：

UPDATE estoque_tamanhos tam
JOIN combo_estoque ce ON tam.estoques_id = ce.estoques_id
SET tam.qtde = tam.qtde - IF(NEW.tipo = 'K', NEW.qtde_prod * NEW.qtde_lote, NEW.qtde_prod)
WHERE ce.produtos_id = NEW.produtos_id
AND UPPER(tam.tamanho) = UPPER(NEW.tamanho_prod);

为什么这个方法可行？ #

• JOIN会把estoque_tamanhos和combo_estoque中所有匹配estoques_id的行关联起来，相当于自动遍历了子查询返回的所有结果
• 这种集合操作是数据库优化器最擅长的，性能比手动循环好很多，也不会出现重复处理的问题（因为每一行匹配一次就更新一次）

特殊场景：如果一定要用循环（不推荐） #

如果因为业务逻辑限制必须用循环处理每一个estoques_id，可以写一个存储过程来实现：

DELIMITER //
CREATE PROCEDURE update_estoque_tamanhos(IN p_produtos_id INT, IN p_tipo VARCHAR(1), IN p_qtde_prod INT, IN p_qtde_lote INT, IN p_tamanho_prod VARCHAR(20))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_estoques_id INT;
    -- 声明游标遍历子查询结果
    DECLARE cur CURSOR FOR SELECT estoques_id FROM combo_estoque WHERE produtos_id = p_produtos_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_estoques_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 执行单条更新
        UPDATE estoque_tamanhos tam
        SET tam.qtde = tam.qtde - IF(p_tipo = 'K', p_qtde_prod * p_qtde_lote, p_qtde_prod)
        WHERE tam.estoques_id = v_estoques_id
        AND UPPER(tam.tamanho) = UPPER(p_tamanho_prod);
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

然后调用这个存储过程：

CALL update_estoque_tamanhos(NEW.produtos_id, NEW.tipo, NEW.qtde_prod, NEW.qtde_lote, NEW.tamanho_prod);

注意事项 #

• 除非万不得已，尽量不要用游标循环，因为数据库的集合操作效率远高于逐行处理
• 游标循环可能会带来锁表、性能低下的问题，尤其是数据量较大的时候

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