mysql 大表删除记录,并保留部分记录。
数据库不忙的时候执行。
操作:
1,生成删除存储过程,P_delete_row(参数),参数为循环删除几次,默认一次删除5000条,
当删除记录为0时,自动退出循环。
2,调用此过程 call P_delete_row(10),执行10个循环。
CREATE DEFINER=`metter`@`%` PROCEDURE `P_delete_row`(next_int int)
BEGIN
#Routine body goes here...
declare i int default 1;
declare j int default 1;
declare delrows_int int default 0; -- 当次删除行变量
declare delrows_sum int default 0; -- 删除行合计
#事务相关变量
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
select 'DELETEING.' AS 'DELETEING ROWS'; -- 提示删除正在执行
while i <= next_int do
#开启事务(重要!不开的话,100w数据需要论天算)
start transaction;
#INSERT INTO data_sync(data_type,object_id,action,action_target_id,create_at)VALUES(i,i,2,i,"2021-3-13 15:00:33");
#①SELECT concat('EXECUTE IS : ', i,' OF ',next_int) AS 'EXECUTEING'; #显示第几次循环
-- 删除语句,限制每次删除5000条
delete from t_report_167 where SampleTime < str_to_date('2019-05-01', '%Y-%m-%d %H:%i:%S') LIMIT 5000;
#返回删除的行数[insert、delete、UPDATE 用select row_count()返回数据;select 用select found_rows()返回数据]
select row_count() into delrows_int; -- 得到当前删除的行
#当删除行为0时,表示没有行要删除了,则标记退出,i设为最后的一个值
IF delrows_int=0 THEN
set i=next_int;
END IF;
#②select delrows_int AS 'DELETED ROWS'; -- 显示删除的行数
#上面两个提示①、②合并,多个提示,放在一行
SELECT concat('EXECUTED IS : ', i,' OF ',next_int) AS 'EXECUTED', delrows_int AS 'DELETED ROWS';
set delrows_sum=delrows_sum + delrows_int; -- 删除行相加
IF t_error = 1 THEN -- 判断事务成功与否
ROLLBACK;
ELSE
COMMIT;
END IF;
#select t_error; //返回标识位的结果集;
set i = i+1;
set t_error=0;
SELECT SLEEP(5) AS 'Waiting'; #暂停5秒,接收数据库其它操作,防止卡死
end while;
SELECT '1 DELETE OK ; END OF EXECUTION DELETED.' AS 'EXECUTEING'; #执行结束
select delrows_sum AS 'DELETED ALL ROWS'; -- 显示删除的行数
-- 下面是执行表收缩的代码,一般大量删除数据后执行
/*
SELECT ' OPTIMIZE TABLE.' AS 'OPTIMIZEING'; #优化执行结束
ALTER TABLE t_report_167 ENGINE = Innodb;
SELECT '2 OPTIMIZE OK ; END OF OPTIMIZE.' AS 'OPTIMIZEING'; #执行结束
*/
END
生成后调用:
call P_delete_row(10),其中的10为循环几次,如果全部有数据删除,最多删除10X5000=50000条记录。
删除时,可以改其要删除的表,及要收缩的表, 删除的条件where最好有索引。
可以在删除中判断,是否有删除记录,没有则退出循环。