|
代码如下：
- delimiter $$
- CREATE PROCEDURE `clean_partiton`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64)，reserve INT)
- BEGIN
注：
- 该储存过程适用于分区字段类型为datetime，按天分区且命名为p20180301格式规范的分区表
- 获取最旧一个分区，判断是否为reserve天前分区，是则进行删除，每次只删除一个分区
- 提前创建14天分区，判断命名不重复则创建
- 创建 history_partition 表，varchar(200)和datetime类型。记录执行成功的SQL语句
- DECLARE PARTITION_NAMES VARCHAR(16);
- DECLARE OLD_PARTITION_NAMES VARCHAR(16);
- DECLARE LESS_THAN_TIMES varchar(16);
- DECLARE CUR_TIME INT;
- DECLARE RETROWS INT;
- DECLARE DROP_PARTITION VARCHAR(16);
- SET CUR_TIME = DATE_FORMAT(NOW,'%Y%m%d');
- BEGIN
- SELECT PARTITION_NAME INTO DROP_PARTITION FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME order by PARTITION_ORDINAL_POSITION asc limit 1 ;
- IF SUBSTRING(DROP_PARTITION,2) < DATE_FORMAT(CUR_TIME - INTERVAL reserve DAY, '%Y%m%d') THEN
- SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' drop PARTITION ', DROP_PARTITION, ';' );
- PREPARE STMT FROM @sql;
- EXECUTE STMT;
- DEALLOCATE PREPARE STMT;
- INSERT INTO history_partition VALUES (@sql, now);
- END IF;
- end;
- SET @__interval = 1;
- create_loop: LOOP
- IF @__interval > 15 THEN
- LEAVE create_loop;
- END IF;
- SET LESS_THAN_TIMES = DATE_FORMAT(CUR_TIME + INTERVAL @__interval DAY, '%Y%m%d');
- SET PARTITION_NAMES = DATE_FORMAT(CUR_TIME + INTERVAL @__interval -1 DAY, 'p%Y%m%d');
- IF(PARTITION_NAMES != OLD_PARTITION_NAMES) THEN
- SELECT COUNT(1) INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND LESS_THAN_TIMES <= substring(partition_description,2,8) ;
- IF RETROWS = 0 THEN
- SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITION_NAMES, ' VALUES LESS THAN ( "',LESS_THAN_TIMES, '" ));' );
- SET @__interval=@__interval+1;
- SET OLD_PARTITION_NAMES = PARTITION_NAMES;
- END LOOP;
- END
- $$
- delimiter ;
（编辑：泰州站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
