MySQL动态表名

MySQL 中SQL语句无法实现动态的使用表名做变量。只能通过PREPARE来实现动态SQL语句。

SET @sql = concat(' select * from a_',DATE_FORMAT(now(),'%y%m'));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

• CONCAT 拼接SQL语句
• PREPARE预编译语句
• EXECUTE执行语句
• DEALLOCATE删除预编译资源

set @aa=concat('select * from ',"a_",(select substring(DATE_FORMAT(Date_add(now(), interval 0 day),'%Y%m'),3,4)));
prepare qq from @aa;
execute qq;

例1：

#动态表名
CREATE PROCEDURE ( table_name varchar(50))
begin
   set @sql=concat('select * from ',table_name);
   PREPARE   stmt   FROM   @sql;
   EXECUTE   stmt   ;
end

例2：

set @aa=CONCAT('select * from ',getApMonthTabName(8));# getApMonthTabName获得表名函数
prepare qq from @aa;
execute qq;

DROP PROCEDURE IF EXISTS pro_ap_month_dtl_day_;
CREATE PROCEDURE pro_ap_month_dtl_day_(in pvAverage VARCHAR (32),in tab_name VARCHAR (32))
BEGIN
set @STMT:=concat("INSERT INTO ",getApMonthTabName(tab_name), #动态表名
" SELECT * FROM wbo_ap_dtl_day a where id = 1");
 PREPARE stmt FROM @STMT;
 EXECUTE stmt ;
end;