mysql 表名作为变量来使用

DECLARE lv_table1 varchar(131);  

DECLARE lv_table2 varchar(131);  

DECLARE lv_table3 varchar(131);  

set lv_table1 = concat('tbl_log_mo_',date_format(DATE_ADD(NOW(),INTERVAL -1 MONTH),'%Y%m'));  

set lv_table2 = concat('tbl_log_mt_',date_format(DATE_ADD(NOW(),INTERVAL -1 MONTH),'%Y%m'));  

set lv_table3 = concat('tbl_log_report_',date_format(DATE_ADD(NOW(),INTERVAL -1 MONTH),'%Y%m'));

  set @sqlStr = CONCAT('create table ',lv_table1,' select * from tbl_log_mo where createtime BETWEEN date_format(DATE_ADD( DATE_ADD(LAST_DAY(NOW()),INTERVAL 1 DAY ),INTERVAL -2 MONTH),''%Y-%m-%d %H:%i:%s'') and date_format(concat(LAST_DAY(DATE_ADD(NOW(),INTERVAL -1 MONTH)),'' 23:59:59''),''%Y-%m-%d %H:%i:%s'')');   

PREPARE stmt from @sqlStr;  

EXECUTE stmt;   /**/  

set @sqlStr = CONCAT('create table ',lv_table2,' select * from tbl_log_mt where submittime BETWEEN date_format(DATE_ADD( DATE_ADD(LAST_DAY(NOW()),INTERVAL 1 DAY ),INTERVAL -2 MONTH),''%Y-%m-%d %H:%i:%s'') and date_format(concat(LAST_DAY(DATE_ADD(NOW(),INTERVAL -1 MONTH)),'' 23:59:59''),''%Y-%m-%d %H:%i:%s'')');   

PREPARE stmt from @sqlStr;  

EXECUTE stmt;    

set @sqlStr = CONCAT('create table ',lv_table3,' select * from tbl_log_report where createtime BETWEEN date_format(DATE_ADD( DATE_ADD(LAST_DAY(NOW()),INTERVAL 1 DAY ),INTERVAL -2 MONTH),''%Y-%m-%d %H:%i:%s'') and date_format(concat(LAST_DAY(DATE_ADD(NOW(),INTERVAL -1 MONTH)),'' 23:59:59''),''%Y-%m-%d %H:%i:%s'')');   

PREPARE stmt from @sqlStr;  

EXECUTE stmt;

转载于:https://www.cnblogs.com/asdf-0310/p/8707162.html