當我執行此查詢時,我收到此錯誤訊息“錯誤代碼: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粉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
#