存储过程自动创建表分区(按时间)

• 存储过程

CREATE PROCEDURE proc_Add_Date_Partition_For_Table(IN BeginDate DATETIME, IN EndDate DATETIME, IN DBName VARCHAR(20),
                                                   IN TableName VARCHAR(50))
  BEGIN

    DECLARE PartitionName varchar(50);
    DECLARE PartitionColumn varchar(50);
    DECLARE PartitionValue Datetime;

    if EndDate is not null then
        select PARTITION_NAME ,REPLACE(PARTITION_EXPRESSION,'`','') as PARTITION_COLUMN,
        str_to_date(SUBSTR(PARTITION_DESCRIPTION FROM 2 FOR 10),'%Y-%m-%d') as PARTITION_VALUE
        into PartitionName,PartitionColumn,PartitionValue
        from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA=DBName and table_name=TableName
        order by PARTITION_ORDINAL_POSITION desc limit 1;

        IF PartitionValue IS NOT NULL THEN
        SET BeginDate = PartitionValue;
        END IF;

        WHILE BeginDate <= EndDate DO
        set @SqlStr=concat('alter table ',DBName,'.',TableName,
        ' add partition(partition p_',
        DATE_FORMAT(BeginDate,'%Y%m%d'),' values less than('',DATE_FORMAT(DATE_ADD(BeginDate,INTERVAL 1 day),'%Y-%m-%d'),''));');

        PREPARE STMT FROM @SqlStr;
        EXECUTE STMT;   

        SET BeginDate = DATE_ADD(BeginDate,INTERVAL 1 day);
        END WHILE;
    end if;

END;

• 参数说明

BeginDate 分区起始时间
EndDate  分区截止时间
DBName  数据库名
TableName  表名

• 创建测试表

CREATE TABLE `test_db`.`test_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `rdCreateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  PRIMARY KEY (`id`,`rdCreateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(rdCreateTime)
(PARTITION p_0 VALUES LESS THAN ('2017-09-01') ENGINE = InnoDB) */;

• 调用存储过程创建分区

call proc_Add_Date_Partition_For_Table('2017-09-02','2019-01-01','test_db','test_account')