mysql循环实测-清理大表数据

1-while循环

create procedure sum1(count int)
begin
    declare sum int default 0;
    declare i int default 1;
    while i<=count do
        set sum=sum+i;
        set i=i+1;
    end while;
    select sum;
end;
-- 执行存储过程
call sum1(100);
-- 删除存储过程
drop procedure if exists sum1

2-loop循环

create procedure sum2(count int)
begin
    declare sum int default 0;
    declare i int default 1;
    loop_name:loop
        if i>count then
        -- 判断条件成立则结束循环  好比java中的 break
        leave loop_name;
        end if;
        set sum=sum+i;
        set i=i+1;
    end loop;
    select sum;
end;
-- 执行存储过程
call sum2(100);
-- 删除存储过程
drop procedure if exists  sum2;

3-repeat循环

create procedure sum3(count int)
begin
        declare sum int default 0;
        declare i int default 1;
        repeat
            set sum=sum+i;
            set i=i+1;
        until i>count end repeat;
        select sum;
end;
-- 执行存储过程
call sum3(100);
-- 删除存储过程
drop procedure if exists sum3;

4-业务实际操作

table_of_big_data ：需要清理的大数据表
primary_id：数据主键id，如果要以primary_id，前提是数据插入是用的 auto_increment(主键自增)，否则应该考虑用 时间 来截断数据

-- 日志表
create table `sp_log` (
    `log_id` bigint(20) not null auto_increment comment '主键id',
    `sp_name` varchar(32) collate utf8_bin not null default '' comment '储存过程名称',
    `msg` varchar(256) collate utf8_bin default null comment '日志内容',
    `creation_time` datetime default current_timestamp comment '创建时间',
    primary key (`log_id`) using btree,
    key `idx_msg_prefix` (`msg` (128))
) engine = innodb auto_increment = 1000 default charset = utf8 collate = utf8_bin row_format = dynamic comment = '存储过程日志表';

-- 创建存储过程
create procedure sp_clean_table_of_big_data(start_id int,batch_size int)
begin
    declare i int default 1;
    declare offset int default -1;
    while (offset < start_id) do
        select @offset := primary_id from table_of_big_data where primary_id < start_id limit batch_size,1;
        set offset = @offset;
        delete from table_of_big_data where primary_id < offset limit batch_size;
        insert into sp_log (sp_name, msg) values ('sp_clean_table_of_big_data', concat('当前第', i, '次执行','删除', batch_size,'条数据'));
        set i = i + 1;
        commit;
    end while;
end;

-- 查找要保留数据的截至primary_id
select primary_id from table_of_big_data order by primary_id desc limit 2000000, 1;

-- 执行存储过程
call sp_clean_table_of_big_data (105408523, 5000);

-- 删除存储过程
drop procedure if exists sp_clean_table_of_big_data;

转载自：https://www.cnblogs.com/better-farther-world2099/articles/10305171.html