|
代码如下：
- 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 ;
（编辑：湖南网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
