mysql使用optimize释放空间

 

 

CREATE PROCEDURE `sp_optimize_table`() 
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=21;
while i<=50 DO
insert into tb_stat_duty_log(dutydate,dutyno,status,remark) values(l_state_date,l_dutyno,0,concat('开始optimize表app_message_',i));
      set @strsql01 = CONCAT('optimize table db_pushmsg.app_message_',i);
      /*set @strsql01 = CONCAT('optimize table db_admin.tb_stat_duty_log');*/
      /*select @strsql01;*/
      PREPARE stmt01 FROM @strsql01;
      execute stmt01;
      deallocate prepare stmt01;

insert into tb_stat_duty_log(dutydate,dutyno,status,remark) values(l_state_date,l_dutyno,1,concat('结束optimize表app_message_',i));
set i = i +1;

end while;

END