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