自动创建表分区存储

mysql 版 运行这个 存储过程会自动把表创建分区 按天创建分区

CREATE DEFINER=`block`@`%` 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;
				set @SqlStr=concat('alter table ',DBName,'.',TableName,
        ' PARTITION BY RANGE(tradeDayId)(partition p_',
        DATE_FORMAT(BeginDate,'%Y%m%d'),' values less than(',DATE_FORMAT(BeginDate,'%Y%m%d'),')');
        WHILE BeginDate <= EndDate DO
        set @SqlStr=concat(@Sqlstr,',partition p_',DATE_FORMAT(DATE_ADD(BeginDate,INTERVAL 1 day),'%Y%m%d'),' values less than(',DATE_FORMAT(DATE_ADD(BeginDate,INTERVAL 1 day),'%Y%m%d'),')');
        SET BeginDate = DATE_ADD(BeginDate,INTERVAL 1 day);
        END WHILE;
				set @SqlStr=concat(@Sqlstr,');');
				PREPARE STMT FROM @SqlStr;
        EXECUTE STMT; 
    end if;
END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28