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')
