MySQL数据库利用存储过程定时分表

ETL通常会往一张表中不断的灌数，但是如果表中数据量庞大会降低效率。

存储过程分表

CREATE DEFINER=`root`@`%` PROCEDURE `fenbiao_auto`()
BEGIN
	#Routine body goes here...
	DECLARE startdate varchar(255); -- 上月第一天
	DECLARE enddate varchar(255);		-- 上月最后一天
	DECLARE lastmonth_num varchar(255);		-- 上月%Y%m
	DECLARE table_dyn text default '';	-- 落地表名称
	
 select DATE_FORMAT(date_add(curdate()-day(curdate())+1,interval -1 month),'%Y%m%d') INTO startdate  FROM DUAL;
 select DATE_FORMAT(last_day(date_sub(now(),interval 1 month)),'%Y%m%d') INTO enddate FROM DUAL;
 select DATE_FORMAT(date_add(curdate()-day(curdate())+1,interval -1 month),'%Y%m') INTO lastmonth_num  FROM DUAL;
	

	
	-- 第一步建表
		SET table_dyn = CONCAT("DROP TABLE IF EXISTS user_voice_info_new_",lastmonth_num);
		SELECT table_dyn;
		SET @sql_text_0=table_dyn;
						PREPARE stmt FROM @sql_text_0;
						EXECUTE stmt;
						DEALLOCATE PREPARE stmt;