MySQL如何每个月自动创建一张表，以年月做为表名

[_大叔_]

MySQL如何每个月自动创建一张表，以年月做为表名，没有接触过MySQL，刚开始接触，但是我在用变量做表名的时候提示出错，该如何解决呢？？有没有大神指导一下！

[九月茅]

提出报什么错误信息？

[_大叔_]

引用 1 楼 mchdba 的回复:

提出报什么错误信息？

感谢你的回复 错误信息如下 [SQL]DECLARE @tableName CHAR(20) set @tableName=CONCAT((select date_format(now(),'%Y')),(select date_format(now(),'%c')),'realdata') create table @tableName ( RTDId int not null PRIMARY key, NIIp varchar(15) not null, State int not null, MonitorTime datetime not null, StateInfo varchar(200) not NULL )engine=innodb default charset=utf8 auto_increment=1; [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @tableName CHAR(20) set @tableName=CONCAT((select date_format(now(),'%' at line 1

[九月茅]

这个要用动态sql来操作的。

[九月茅]

动态SQL参考：http://blog.csdn.net/mchdba/article/details/15499489

[_大叔_]

引用 4 楼 mchdba 的回复:

动态SQL参考：http://blog.csdn.net/mchdba/article/details/15499489

感谢你的回复

[ACMAIN_CHM]

先用字符串操作生成需要的 create table 语句，然后用 prepare / execute 来执行。具体可以参考MYSQL官方手册中的 prepare / execute 的例子。

[小灰狼]

我正好有楼主类似的需求，每个季度为几个表增加一个分区，表的基本名称是在一个叫设备类型的表里，每天计划执行一个过程，在过程里从系统表中判断是否已经创建了相关的分区，如果没创建就创建它 楼主可以参考一下，记得在my.ini 文件里配置event_scheduler=on

/**
定时每天检查各个设备类型的历史数据表，如果历史数据表的所在分区已经
接近当前日期，则为此设备类型追加分区
*/

-- 得到按月分区的日期值
delimiter ;
drop function if exists fnGetPartitionDateForMonth; 
delimiter ;;
create function fnGetPartitionDateForMonth() returns INT
begin
	declare v_today datetime default date_add(now(), INTERVAL 2 month);
	return year(v_today) * 100 + month(v_today);
end;;

-- 得到按季度分区的日期值
delimiter ;
drop function if exists fnGetPartitionDateForQuarter;
delimiter ;;
create function fnGetPartitionDateForQuarter() returns int
begin
	declare v_today datetime default date_add(now(), interval 3 month);
	declare v_month int;
	
	set v_month = month(v_today);
	if v_month = 1 or v_month = 2 or v_month = 3 then 
		set v_today = DATE_ADD(v_today, INTERVAL (4 - v_month) month);
	elseif v_month = 4 or v_month = 5 or v_month = 6 THEN
		set v_today = DATE_ADD(v_today, INTERVAL (7 - v_month) month);
	elseif v_month = 7 or v_month = 8 or v_month = 9 THEN
		set v_today = date_add(v_today, INTERVAL (10 - v_month) month);
	ELSE
		set v_today = date_add(v_today, INTERVAL (13 - v_month) month);
	end if;
	
	return year(v_today) * 100 + month(v_today);
end;;

-- 得到按半年分区的日期值
delimiter ;
drop function if exists fnGetPartitionDateForHalfYear;
delimiter ;;
create function fnGetPartitionDateForHalfYear() returns int
begin
	declare v_today datetime default date_add(now(), interval 6 month);
	declare v_month int;
	
	set v_month = month(v_today);
	
	if v_month <= 6 THEN
		set v_today = date_add(v_today, INTERVAL (7 - v_month) month);
	else
		set v_today = DATE_ADD(v_today, INTERVAL (13 - v_month) month);
	end if;
	
	return year(v_today) * 100 + month(v_today);
end;;

-- 维护按年分区
delimiter ;
drop function if exists fnGetPartitionDateForYear;
delimiter ;;
create function fnGetPartitionDateForYear() returns int
begin
	declare v_today datetime default date_add(now(), INTERVAL 2 year);
	return year(v_today) * 100;
end;;


delimiter ;
drop procedure if exists spMaintainPartitions;
delimiter ;;
create procedure spMaintainPartitions()
BEGIN
	
	declare v_sql varchar(2000);
	declare v_cnt int;
	declare v_deviceTypeId int;
	declare v_tablename varchar(50);
	declare v_tablename_analog varchar(50);
	declare v_tablename_digital varchar(50);
	declare v_partitionType int;
	declare v_fileDir varchar(1000);
	declare v_tablenames varchar(1000) default '';
	declare v_intDate int;
	declare v_partitionName varchar(100);
	declare done int default 0;
	declare c_deviceType cursor 
		for select Id, TableName, PartitionType, DataFileDir
				from tbDeviceType 
				where Generated = 1;
	declare continue handler for not found set done = 1;
	
	insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
			Values(Now(), 'spMaintainPartitions start......');
	
	open c_deviceType;
	deviceType_loop: LOOP
		
		fetch c_deviceType into v_deviceTypeId, v_tablename, v_partitionType, v_fileDir;
		
		set v_fileDir = replace(v_fileDir, '\\', '/');
		if locate(':', v_fileDir) > 0 and locate(':/', v_fileDir) = 0 then
			set v_fileDir = replace(v_fileDir, ':', ':/');
		end if;
		
		if done = 1 then 
			leave deviceType_loop;
		end if;
		
		set v_intDate = null;
		if v_partitionType = 1 then 
			set v_intDate = fnGetPartitionDateForMonth();
		ELSEIF v_partitionType = 2 THEN
			set v_intDate = fnGetPartitionDateForQuarter();
		ELSEIF v_partitionType = 3 then 
			set v_intDate = fnGetPartitionDateForHalfYear();
		elseif v_partitionType = 4 then 
			set v_intDate = fnGetPartitionDateForYear();
		end if;
		
		if v_intDate is null then
			insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`) 
					values(Now(), Concat('DeviceTypeId = ', cast(v_deviceTypeId As char(10)), ' did not define paritition schedule'));
		else 
			
			set v_partitionName = concat('p', cast(v_intDate as char(6)));
			
			-- 模拟量表
			set v_tablename_analog = concat(v_tablename, '_Analog');
			select count(*) into v_cnt
				from information_schema.`TABLES` where `TABLE_SCHEMA` = database() and `table_name` = v_tablename_analog;
			

			if v_cnt > 0 then

				select count(*) into v_cnt
				from 
					information_schema.`PARTITIONS` 
				where 
					TABLE_SCHEMA = database() and table_name = v_tablename_analog and partition_name = v_partitionName;
				
				if v_cnt = 0 then
					set v_sql = CONCAT('alter table ', v_tablename_analog, ' add partition (partition ', v_partitionName, ' values less than (', cast(v_intDate as char(6)), ') data directory = ''', v_fileDir, ''' index directory = ''', v_fileDir , ''')');
					insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
							Values(Now(), concat('sql = ''', v_sql));
					
					set @sql = v_sql;
					prepare cmd from @sql;
					execute cmd;
					deallocate prepare cmd;
					
					insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`) 
						values(Now(), concat('execute complete: ', v_sql));
				end if;
			end if;
			
			-- 数字量表
			set v_tablename_digital = concat(v_tablename, '_Digital');
			select count(*) into v_cnt
				from information_schema.`TABLES` where `TABLE_SCHEMA` = database() and `table_name` = v_tablename_digital;
			
			if v_cnt > 0 then

				select count(*) into v_cnt
				from 
					information_schema.`PARTITIONS`
				where 
					TABLE_SCHEMA = database() and table_name = v_tablename_digital and partition_name = v_partitionName;
				
				if v_cnt = 0 then 
					set v_sql = CONCAT('alter table ', v_tablename_digital, ' add partition (partition ', v_partitionName, ' values less than (', cast(v_intDate as char(6)), ') data directory = ''', v_fileDir, ''' index directory = ''', v_fileDir , ''')');
					
					insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
							Values(Now(), concat('sql = ''', v_sql));
					
					set @sql = v_sql;
					prepare cmd from @sql;
					execute cmd;
					deallocate prepare cmd;
					
					insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`) 
						values(Now(), concat('execute complete: ', v_sql));
					
				end if;
			end if;
			
		end if;
		
	end loop deviceType_loop;
	close c_deviceType;
	
END;;

delimiter ;

drop event if exists e_DataPartitionMaintain;
create event e_DataPartitionMaintain
	on SCHEDULE every 60 Second
	on completion PRESERVE
	do call spMaintainPartitions();

set global event_scheduler = on;

[_大叔_]

感谢大家的热心帮助