Solve the error that mysql result contains multiple lines

Question

When I execute this query, I get this error message "Error code: 1172. The result contains more than one row"

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

Is it helpful that

id is the primary key of the table below?

Answer 1

Your local variable has the same name as the table column. This way you never compare the local variable to the column, but always to the local variable itself.

Your query needs to return exactly one row to provide the id variable

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 and user_following_id are interpreted as local variables in all instances and are therefore translated as follows

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

Returns all rows of user_following. To fix this, rename your local variables like

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

(Assuming there is no column named local_user_been_following_id or local_user_following_id on table user_following)

See also here: https://dev.mysql.com/doc/ refman/8.0/en/local-variable-scope.html