mysql循环删除表数据，避免一次性删除大量数据导致数据库故障

-- 创建存储过程
-- 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);