我有存储过程:

BEGIN
SELECT @id_for_del := ful.social_id
FROM facebook_users_likes ful
where
(select count(*) from facebook_users fu where fu.social_id = ful.social_id) = 0;
IF NOT @id_for_del IS NULL
THEN
DELETE from facebook_users_likes WHERE social_id = @id_for_del;
END IF;
END


Select返回我46 000行,但每个执行存储过程仅删除一行。
那么,如何一次删除所有46 000行?

最佳答案

这是selectdelete的直接转换:

DELETE FROM facebook_users_likes ful
WHERE (select count(*) from facebook_users fu where fu.social_id = ful.social_id) = 0;


在这种情况下使用not exists效率更高:

DELETE FROM facebook_users_likes ful
WHERE NOT EXISTS (select 1 from facebook_users fu where fu.social_id = ful.social_id);


如果在facebook_users(social_id)上具有索引,则尤其如此。

关于mysql - MYSQL存储过程删除,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23408845/

10-09 00:37