mysql 存储过程 动态建表_mysql实现创建动态表的存储过程

前天做系统遇到这样一个需求，需要对后台数据定期做数据归档任务，把一张表的是数据在每天半夜将前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('crea