解决mysql结果包含多行错误

Question

当我执行此查询时，我收到此错误消息“错误代码：1172。结果包含多个行”

CREATE DEFINER=`root`@`localhost` PROCEDURE `un_follow`(
  user_been_following_id int,
  user_following_id int
)
BEGIN
    declare id int;
    select following_id into id from user_following
        where user_been_following_id = user_been_following_id
        and  user_following_id =  user_following_id; 
        
    delete from user_following 
    where following_id = id;
END

id 是下表的主键有帮助吗？

Answer 1

您的局部变量与表列同名。 这样，您就永远不会将局部变量与列进行比较，而始终将局部变量与局部变量本身进行比较。

您的查询需要恰好返回一行来提供 id 变量

select following_id into id from user_following
    where user_been_following_id = user_been_following_id
    and  user_following_id =  user_following_id;

user_been_following_id 和 user_following_id 在所有实例中都被解释为局部变量，因此翻译如下

select following_id into id from user_following
    where 1 = 1
    and  1 = 1;

其中返回 user_following 的所有行。要解决此问题，请重命名您的局部变量，例如

CREATE DEFINER=`root`@`localhost` PROCEDURE `un_follow`(
  local_user_been_following_id int,
  local_user_following_id int
)
BEGIN
    declare id int;
    select following_id into id from user_following
        where user_been_following_id = local_user_been_following_id
        and  user_following_id =  local_user_following_id; 
    
    delete from user_following 
    where following_id = id;
END

（假设表 user_following 上没有名为 local_user_been_following_id 或 local_user_following_id 的列）

另请参阅此处： https://dev.mysql.com/doc/ refman/8.0/en/local-variable-scope.html