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

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

我正好有楼主类似的需求，每个季度为几个表增加一个分区，表的基本名称是在一个叫设备类型的表里，每天计划执行一个过程，在过程里从系统表中判断是否已经创建了相关的分区，如果没创建就创建它
楼主可以参考一下，记得在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;