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;
