mysql动态执行sql批量删除数据
时间：2021-07-01 10:21:17
帮助过：4人阅读
PROCEDURE `sp_delete_pushmsg_data`()
BEGIN
declare l_delete_date
varchar(
16);
declare l_state_date
varchar(
16);
declare l_dutyno
int;
declare l_row_cnt
int DEFAULT 0;
declare rn
int default 0;
declare i
int default 0;
set l_dutyno
=101;
set l_delete_date
=date_format(DATE_ADD(now(),INTERVAL
-30 day),
‘%Y-%m-%d‘);
/*删除90天之前的*/
set l_state_date
=date_format(now(),
‘%Y%m%d‘);
set i
=0;
/*
set @strsql01 = CONCAT(‘delete from db_pushmsg.app_message_‘,i,‘ where create_time <= ‘‘2018-06-24 00:00:00‘‘ and message_type not in (1,2,3,4,5,6,7,8,9, 4096, 4097, 4098, 24576, 24577,24578, 28672, 28673, 36865, 4353, 4354) limit 1000‘);
select @strsql01;
*/
while i
<=10 DO
insert into tb_stat_duty_log(dutydate,dutyno,status,remark)
values(l_state_date,l_dutyno,
0,concat(
‘开始清理‘,i));
label:
WHILE (
1 = 1)
DO
set @strsql01 = CONCAT(
‘delete from db_test.tb_test‘,i,
‘ where create_time <= ‘‘2018-06-24 00:00:00‘‘ and message_type not in (1,2,3,4,5,6,7,8,9, 4096, 4097, 4098, 24576, 24577,24578, 28672, 28673, 36865, 4353, 4354) limit 1000‘);
/*select @strsql01;*/
PREPARE stmt01
FROM @strsql01;
execute stmt01;
SET @aa = ROW_COUNT();
select @aa;
IF @aa = 0 THEN
LEAVE label;
END IF;
deallocate prepare stmt01;
END WHILE;
deallocate prepare stmt01;
set i
= i
+1;
insert into tb_stat_duty_log(dutydate,dutyno,status,remark)
values(l_state_date,l_dutyno,
0,concat(
‘结束清理‘,i));
end while;
END
mysql动态执行sql批量删除数据
标签：nbsp limit message mys proc 开始 count() date arch
