目录
目的表:sp_program_playinfo
目的字段:create_time
主键 :id
表为已创建表
查询当前表是否存在分区
SELECT
partition_name
FROM
information_schema.PARTITIONS
WHERE
table_schema = SCHEMA ( )
AND table_name = 'sp_program_playinfo'
AND partition_name LIKE 'p2019%';;
1.分区
执行修改分区语句会提示:1503 - A PRIMARY KEY must include all columns in the table's partitioning function
意思是 主键必须包含表的分区函数中的所有列,所以我们将该列改为联合主键。
alter TABLE `sp_program_playinfo` PARTITION BY RANGE (TO_DAYS(create_time))
(
PARTITION p20180601 VALUES LESS THAN (TO_DAYS('20180601')) ENGINE = InnoDB,
PARTITION p20180701 VALUES LESS THAN (TO_DAYS('20180701')) ENGINE = InnoDB,
PARTITION p20180801 VALUES LESS THAN (TO_DAYS('20180801')) ENGINE = InnoDB,
PARTITION p20180901 VALUES LESS THAN (TO_DAYS('20180901')) ENGINE = InnoDB,
PARTITION p20181001 VALUES LESS THAN (TO_DAYS('20181001')) ENGINE = InnoDB,
PARTITION p20181101 VALUES LESS THAN (TO_DAYS('20181101')) ENGINE = InnoDB,
PARTITION p20181201 VALUES LESS THAN (TO_DAYS('20181201')) ENGINE = InnoDB,
PARTITION p20190101 VALUES LESS THAN (TO_DAYS('20190101')) ENGINE = InnoDB,
PARTITION p20190201 VALUES LESS THAN (TO_DAYS('20190201')) ENGINE = InnoDB,
PARTITION p20190301 VALUES LESS THAN (TO_DAYS('20190301')) ENGINE = InnoDB,
PARTITION p20190401 VALUES LESS THAN (TO_DAYS('20190401')) ENGINE = InnoDB
);
Range() 参数为目的列所产生的值,TO_DAYS(日期值)是mysql 函数:返回一个天数(从公元0年到目的值的天数);
PARTITION 分区名称 VALUES LESS THAN (区间值) ENGINE = InnoDB,
由于我的表分区需要定时动态自增,所有上面的语句到了20190401以后就会出错,因为20190401以后的时间没有分配到分区。如果需要进行完整分区可以加上
PARTITION pMaxValue VALUES LESS THAN MAXVALUE
2.分区定时任务:存储过程与定时任务
1.存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `autoPartition`(IN table_name VARCHAR(64),OUT theSql VARCHAR(200))
BEGIN
# 目标分区数
DECLARE target_PCount INT DEFAULT -1;
DECLARE newPName VARCHAR(21);
DECLARE target_Date TIMESTAMP;
SET target_Date = DATE_ADD(CURRENT_DATE(),INTERVAL 1 DAY);
SET newPName = DATE_FORMAT( target_Date, 'p%Y%m%d' );
select COUNT(1) c INTO target_PCount from information_schema.partitions where table_schema = schema() and table_name= table_name AND partition_name = newPName;
IF target_PCount <= 0 THEN
SET @createPartitonSQL = CONCAT('ALTER TABLE ',table_name,' add PARTITION( PARTITION ' , newPName,
' VALUES LESS THAN (TO_DAYS("', target_Date , '")) ENGINE = InnoDB);' );
PREPARE STMT FROM @createPartitonSQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
SELECT @createPartitonSQL INTO theSql;
END IF;
END
2.创建 定时器计划
create EVENT `PartitionTest`.`autoCreatePartition_sp_program_playinfo`
ON SCHEDULE
EVERY '7' WEEK STARTS '2019-01-01 00:00:00'
DO call createPartition('sp_program_playinfo') ;