mysql动态执行sql批量删除数据

 

CREATE 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 

 

sql执行结果返回给到变量

CREATE DEFINER=`root`@`192.168.2.84` PROCEDURE `sp_check_data_bysql`()
BEGIN
    #Routine body goes here...
declare i int;
declare l_state_date  varchar(16);
declare l_dutyno      int;
set l_dutyno=101;

set l_state_date = '20250421';
set i = 0;

while i<=1 DO
      set @strsql01 = CONCAT('select count(1) into @mycnt from tb_test_',i,' where 1=1');
      /*SET @mycnt = 0;*/ 
      /*select @strsql01;*/
      
      PREPARE stmt01 FROM @strsql01;
        execute stmt01;
      deallocate prepare stmt01;
      select @mycnt;
insert into tb_stat_duty_log(dutydate,dutyno,status,remark) values(l_state_date,l_dutyno,1,concat('表:',i,' 查询结果:',@mycnt));
set i = i +1;

end while;

END