mysql表删除后自动新建_MySQL如何每天自动创建一张表并删除昨天的表，以年月日做为表名,有没有大神指导一下！...

展开全部

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

/**定时每天检查各个设备类型的历史数据表，如果历史数据表的所在分区已经接近当前日期，则为此设备类型追加分区*/ -- 得到按月分区的日期值delimiter ;drop function if exists fnGetPartitionDateForMonth; delimiter ;;create function fnGetPartitionDateForMonth() returns INTbegin 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 intbegin 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 intbegin 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 intbegin 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;

已赞过

已踩过<

你对这个回答的评价是？

评论

收起