-- 创建存储过程
-- beginid 开始id
-- endid 结束id
CREATE DEFINER=`xiaomao`@`%` PROCEDURE `delOrderHistory`(`beginid` bigint,`endid` bigint)
BEGIN
declare i int default 1;
declare r int default 0;
declare bb int default 0;
set bb = beginid;
-- 每次删除一万条,不影响线上业务,解决一次性删除太多数据导致mysql卡死
while beginid<endid+1 do
set beginid = beginid+10000;
IF beginid>=endid THEN
set beginid = endid+1;
END IF;
delete from orderHistory where id < beginid;
select CONCAT('delete from orderHistory where id <= ',beginid) as 'PROCEDURE',endid-beginid as '剩余条数';
end while;
select CONCAT('完成:(',endid-bb,')条!') as 'RESULT';
END
进入mysql控制台,执行存储过程
mysql> call delOrderHistory(1,12306);