前天做系统遇到这样一个需求，需要对后台数据定期做数据归档任务，把一张表的是数据在每天半夜将前n（可配置）天的数据进行归档，至于归档到哪一张表需要根据当天属于哪一个月来确定表的名字.
 
drop procedure if exists  dataFile;
create procedure dataFile()
BEGIN
declare tableName varchar(100);
declare total int default 0 ;
declare intervalDay int default 7;
select pmt_val  into intervalDay  from postmail.dim_sys_pmt where pmt_name='DATA_FILE_INTERVAL' ;
set tableName=concat('dim_mailsendback_', year(now()),month(now()));
set @sql1=concat("select count(*) into @total from  information_schema.tables where table_name ='",tableName,"' ");
set @sql2=concat('insert into  postmail_back. ', tableName, ' select * from postmail.dim_mailsendback where (dayofyear(now())-dayofyear(sendtime)<=',intervalDay,')');
set @sql3=concat('create table postmail_back. ', tableName, ' select * from postmail.dim_mailsendback where (dayofyear(now())-dayofyear(sendtime)<=',intervalDay,')');
 prepare stmt1 from @sql1;
execute stmt1;
deallocate prepare stmt1;
set total=@total;
if total=0 then
  prepare stmt3 from @sql3;
 execute stmt3;
        deallocate prepare stmt3;
  end if;
if total=1 then
  prepare stmt2 from @sql2;
 execute stmt2;
        deallocate prepare stmt2;
  end if;
END