搜尋

首頁  >  問答  >  主體

解決mysql結果包含多行錯誤

當我執行此查詢時,我收到此錯誤訊息“錯誤代碼: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 ​​是下表的主鍵有幫助嗎?

P粉068174996P粉068174996229 天前419

全部回覆(1)我來回復

  • P粉322319601

    P粉3223196012024-04-05 13:11:10

    您的局部變數與表格列同名。 這樣,您就永遠不會將局部變數與列進行比較,而始終將局部變數與局部變數本身進行比較。

    您的查詢需要恰好回傳一行來提供 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

    #

    回覆
    0
  • 取消回覆