mysql 大表删除记录，并保留部分记录

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最好有索引。

可以在删除中判断，是否有删除记录，没有则退出循环。