mysql 存储过程大数据量表内连删除

最新推荐文章于 2024-12-24 12:27:12 发布

原创最新推荐文章于 2024-12-24 12:27:12 发布 · 399 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

13 篇文章

订阅专栏

本文介绍了在MySQL的mgr管理模式中，当数据量超过145M导致删除操作失败的问题，提出通过创建存储过程来分批删除数据的策略。存储过程根据传入的批量大小动态计算循环次数和删除范围，确保安全高效地完成删除任务。

mysql在mgr模式中删除数据时，数据量大于145M会报错，为了快速实现功能，采取存储过程的方式实现。

-- 功能：删除t_mytable表中和t_mytable_bak重复的部分
-- 重写结束符号，改为$
delimiter $
-- batchSize作为入参传入
create procedure t_mytable(in bs int)
begin
  -- 声明增量表数据总量
  -- 声明循环次数
  -- 声明删除索引开始位置
  -- 声明删除索引结束位置
  -- 声明整数部分
  -- 声明余数部分
  -- 声明记录循环次数，用于计算偏移量
  -- （这个jb玩意只能在begin后面声明一次，重复声明会报错，declare 位置不正确）
  declare total, times, beginIndex, endIndex, divres, modres, repeats int default 0;
  -- t_mytable_bak表作为增量表，需要从t_mytable中删除，统计总数
  select count(1) into total from t_mytable_bak;
  select total as '增量表总行数';
  -- 如果批量数大于增量表总数，则循环一次，开始索引位置是0，结束位置和数据量一致
  if bs>=total
    then
	  set times=1;
	  set beginIndex=0;
	  set endIndex=total;
  else
    -- 计算循环次数，如果能整除则循环次数为整数，否则为整数+1
    set divres=(total div bs);
    set modres=(total mod bs);
    select divres as '整数部分', modres as '余数部分';
	-- 等于用单等号，否则报语法错误，sql比较返回值为0/1  0false 1true
    if modres=0
      then
        set times=divres;
        set beginIndex=0;
        set endIndex=bs;
    else
      set times=divres+1;
      set beginIndex=0;
      set endIndex=bs;
    end if;
  end if;
  select times as '循环次数'; 
  -- 防止客户端限制删除
  set sql_safe_updates=0;
  -- 默认值是0，需要改成1，用于计算偏移量
  set repeats=1;
  while times>0 do
    set repeats=repeats+1;
	delete from t_mytable
	      where (index1, index2)
		     in (
			   -- mysql需要再包一层，否则会报语法不支持的错误
			   select index1, index2 from (
			     -- 用索引升序order by，保证每次limit 的内容顺序是正确的，而且是用索引排序对速度影响不大
			     select index1, index2 from t_mytable_bak order by index1, index2 limit beginIndex, endIndex
			   ) a
			 );
  end while;
  -- 显示总计循环的次数
  select (repeats-1) as '累积循环次数';  
end $
delimiter ;