首页 > 数据库技术 > 详细

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

时间:2019-06-25 16:40:32      阅读:171      评论:0      收藏:0      [点我收藏+]

标签:for   执行sql   eal   har   class   create   time   form   mes   

 

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 

 

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

标签:for   执行sql   eal   har   class   create   time   form   mes   

原文:https://www.cnblogs.com/hxlasky/p/11083514.html

(0)
(0)
   
举报
评论 一句话评论(0
登录后才能评论!
© 2014 bubuko.com 版权所有 鲁ICP备09046678号-4
打开技术之扣,分享程序人生!
             

鲁公网安备 37021202000002号