存储过程执行时无视WHERE子句删除qrcode表全部行求助

问题根源 #

你的存储过程参数名ID与表qrcode的主键列名id冲突了。MySQL默认对标识符（表名、列名、参数名）不区分大小写，因此在DELETE FROM qrcode where id=ID;语句中，ID会被MySQL解析为表的列名id，而非传入的存储过程参数。最终条件变为id=id，这个条件对所有行都成立，导致全表被删除。

而你手动执行的SQL使用的是用户变量@ID，它与列名id不会产生混淆，所以能正确匹配指定行完成删除。

修复方案 #

修改存储过程的参数名，避免与表列名重复，例如将参数改为p_id，同时替换所有引用该参数的位置。此外，原存储过程中SELECT @AFFECTED_ROWS;是错误的，应直接查询OUT参数AFFECTED_ROWS而非用户变量。

修正后的存储过程代码：

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_qrcode`(IN p_id INT, OUT AFFECTED_ROWS INT)
BEGIN

DECLARE EXIT HANDLER FOR sqlexception
BEGIN
    ROLLBACK;
    RESIGNAL;
END;
    
SELECT concat('ID=', p_id) as log;

START TRANSACTION;

SET FOREIGN_KEY_CHECKS=0;
UPDATE `qrcode` SET `parentId`=NULL WHERE `parentId`=p_id;
SET FOREIGN_KEY_CHECKS=1;

DELETE FROM `qrcode` WHERE `id`=p_id;

SET AFFECTED_ROWS = ROW_COUNT();
SELECT AFFECTED_ROWS;

COMMIT;

END

额外说明 #

如果不想修改参数名，也可以在存储过程中给参数添加明确的限定符，或者将参数转换为用户变量使用，但修改参数名是最直观且不易出错的方案。

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