DELIMITER $$DROP PROCEDURE IF EXISTS 'delrows'$$CREATE PROCEDURE delrows() BEGIN DECLARE rn int default 0;SELECT count(*) into rn FROM a u,b p WHERE p.userid=u.userid;WHILE (rn >=0) DODELETE FROM a WHERE userid IN ( SELECT userid FROM B WHERE a.id=b.id limit 10000); SET rn=rn-10000; commit;END WHILE;END $$ DELIMITER ;这样对不?
mysql> call delrows(); ERROR 1305 (42000): PROCEDURE china.delrows does not exist mysql> call delrows(tmp_lastip25,tmp_lastip); ERROR 1305 (42000): PROCEDURE china.delrows does not exist 在调用时报错
delete a from a inner join b on a.id=b.id
DELETE FROM a
INNER JONN b
ON a.id=b.id
是不是占用CPU、I、O很高
一般是用游标处理。对B表打开游标,然后逐个删除A表中ID对应的记录。每删除一个提交一次。
BEGIN
DECLARE rn int default 0;SELECT count(*) into rn FROM a u,b p WHERE p.userid=u.userid;WHILE (rn >=0) DODELETE FROM a WHERE userid IN ( SELECT userid FROM B WHERE a.id=b.id limit 10000);
SET rn=rn-10000;
commit;END WHILE;END
$$
DELIMITER ;这样对不?
ERROR 1305 (42000): PROCEDURE china.delrows does not exist
mysql> call delrows(tmp_lastip25,tmp_lastip);
ERROR 1305 (42000): PROCEDURE china.delrows does not exist
在调用时报错