mysql自动新建分区

新建存储过程create_partition_nextday

功能描述：每次执行一次读取当前日期，并将日期增加一天后，新建分区

L_END:BEGIN     
    DECLARE P_NAME VARCHAR(255) DEFAULT 0;       
    DECLARE P_DESCRIPTION VARCHAR(255) DEFAULT 0;   
    DECLARE ISEXIST_PARTITION VARCHAR(255) DEFAULT 0; 
 
    SELECT PARTITION_NAME INTO ISEXIST_PARTITION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName  LIMIT 1 ;
    --###此处判断表是否为（支持）分区表的 
    IF ISEXIST_PARTITION <=> "" THEN
      SELECT "Partition table not is exist" AS "*****ERROR*****";
      LEAVE  L_END;
    END IF;
 
    SET P_DESCRIPTION =(select date_add(CURDATE(), interval 1 day));	--###此处将当前日期增加一天，作为分区名称
    SET PARTITION_TWO_DAY =(select date_add(CURDATE(), interval 2 day));
    SET P_NAME = REPLACE(P_DESCRIPTION, '-', '');
    SET @S=CONCAT('ALTER TABLE ',tableName,' ADD PARTITION  (PARTITION p',P_NAME,' VALUES LESS THAN (\'',PARTITION_TWO_DAY,'\'))');
    SELECT @S;
    PREPARE stmt2 FROM @S;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;         
END L_END

手动增加分区

ALTER TABLE hdfs_test add PARTITION (PARTITION p00000006  VALUES LESS THAN ('2018-06-12'));

手动调用存储过程

MySQL [sw]> call create_partition_nextday("sw","hdfs_test");

 

创建事件

 

 

 

查看分区

SELECT  
  partition_name part,   
  partition_expression expr,   
  partition_description descr,   
  table_rows   
FROM  
  INFORMATION_SCHEMA.partitions   
WHERE  
  TABLE_SCHEMA = schema()   
  AND TABLE_NAME='hdfs_test'; 

分区建表

create table hdfs_test(  
dt datetime,  
AddBlockNumOps                bigint,  
BlockReceivedAndDeletedNumOps bigint,  
CompleteNumOps                bigint,  
CreateNumOps                  bigint,  
DeleteNumOps                  bigint,  
GetFileInfoNumOps             bigint,  
RenameNumOps                  bigint,  
SendHeartbeatNumOps           bigint,  
AddBlockAvgTime                double,  
BlockReceivedAndDeletedAvgTime double,  
CompleteAvgTime                double,  
CreateAvgTime                  double,  
DeleteAvgTime                  double,  
GetFileInfoAvgTime             double,  
RenameAvgTime                  double,  
SendHeartbeatAvgTime           double  
)PARTITION BY RANGE COLUMNS(dt)(
PARTITION p00000001 VALUES LESS THAN ('2018-06-07'),
PARTITION p00000002 VALUES LESS THAN ('2018-06-08'),
PARTITION p00000003 VALUES LESS THAN ('2018-06-09'),
PARTITION p00000004 VALUES LESS THAN ('2018-06-10'),
PARTITION p00000005 VALUES LESS THAN ('2018-06-11')
);

每次批量新建100个分区

L_END:BEGIN
	#Routine body goes here...
  DECLARE MAX_PARTITION_DESCRIPTION VARCHAR(255) DEFAULT 0;
     DECLARE P_NAME VARCHAR(255) DEFAULT 0;       
     DECLARE P_DESCRIPTION VARCHAR(255) DEFAULT 0;   
     DECLARE i INT DEFAULT 1;
     DECLARE ISEXIST_PARTITION VARCHAR(255) DEFAULT 0; 
     SELECT PARTITION_NAME INTO ISEXIST_PARTITION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName  LIMIT 1 ;
      
     IF ISEXIST_PARTITION <=> "" THEN
       SELECT "Partition table not is exist" AS "*****ERROR*****";
       LEAVE  L_END;
     END IF;
 
     SELECT partition_description INTO MAX_PARTITION_DESCRIPTION  FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName ORDER BY partition_description DESC LIMIT 1;
       
       
     IF MAX_PARTITION_DESCRIPTION <=> "" THEN
       SELECT "Partition table is error" AS "*****ERROR*****";
       LEAVE  L_END;
     END IF;

      
    SET MAX_PARTITION_DESCRIPTION = REPLACE(MAX_PARTITION_DESCRIPTION, '\'', '');
    WHILE i <= 100 DO
           SET P_DESCRIPTION = adddate(MAX_PARTITION_DESCRIPTION, INTERVAL i day);
           SET P_NAME = REPLACE(P_DESCRIPTION, '-', '');
           SET @S=CONCAT('ALTER TABLE ',tableName,' ADD PARTITION  (PARTITION p',P_NAME,' VALUES LESS THAN (\'',P_DESCRIPTION,'\'))');
           SELECT @S;
           PREPARE stmt2 FROM @S;
           EXECUTE stmt2;
           DEALLOCATE PREPARE stmt2;
           SET i = i + 1 ;
    END WHILE;   

END

 

命令行新建存储过程

 

DELIMITER $$
use dnjmx$$
CREATE PROCEDURE create_partition_nextday(
IN databaseName VARCHAR(50),
IN tableName VARCHAR(50))
begin
L_END:BEGIN     
    DECLARE P_NAME VARCHAR(255) DEFAULT 0;       
    DECLARE P_DESCRIPTION VARCHAR(255) DEFAULT 0;
    DECLARE PARTITION_TWO_DAY VARCHAR(255) DEFAULT 0;
    DECLARE ISEXIST_PARTITION VARCHAR(255) DEFAULT 0; 
 
    SELECT PARTITION_NAME INTO ISEXIST_PARTITION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName  LIMIT 1 ;
    IF ISEXIST_PARTITION <=> "" THEN
      SELECT "Partition table not is exist" AS "*****ERROR*****";
      LEAVE  L_END;
    END IF;
 
    SET P_DESCRIPTION =(select date_add(CURDATE(), interval 1 day));
    SET PARTITION_TWO_DAY =(select date_add(CURDATE(), interval 2 day));
    SET P_NAME = REPLACE(P_DESCRIPTION, '-', '');
    SET @S=CONCAT('ALTER TABLE ',tableName,' ADD PARTITION  (PARTITION p',P_NAME,' VALUES LESS THAN (\'',PARTITION_TWO_DAY,'\'))');
    SELECT @S;
    PREPARE stmt2 FROM @S;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;         
END L_END;
END $$

DELIMITER $$
use dnjmx$$
CREATE PROCEDURE create_partition_100day(
IN databaseName VARCHAR(50),
IN tableName VARCHAR(50))
begin
L_END:BEGIN
	#Routine body goes here...
  DECLARE MAX_PARTITION_DESCRIPTION VARCHAR(255) DEFAULT 0;
     DECLARE P_NAME VARCHAR(255) DEFAULT 0;       
     DECLARE P_DESCRIPTION VARCHAR(255) DEFAULT 0;   
     DECLARE i INT DEFAULT 1;
     DECLARE ISEXIST_PARTITION VARCHAR(255) DEFAULT 0; 
     SELECT PARTITION_NAME INTO ISEXIST_PARTITION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName  LIMIT 1 ;
      
     IF ISEXIST_PARTITION <=> "" THEN
       SELECT "Partition table not is exist" AS "*****ERROR*****";
       LEAVE  L_END;
     END IF;
 
     SELECT partition_description INTO MAX_PARTITION_DESCRIPTION  FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName ORDER BY partition_description DESC LIMIT 1;
       
       
     IF MAX_PARTITION_DESCRIPTION <=> "" THEN
       SELECT "Partition table is error" AS "*****ERROR*****";
       LEAVE  L_END;
     END IF;
 
      
    SET MAX_PARTITION_DESCRIPTION = REPLACE(MAX_PARTITION_DESCRIPTION, '\'', '');
    WHILE i <= 100 DO
           SET P_DESCRIPTION = adddate(MAX_PARTITION_DESCRIPTION, INTERVAL i day);
           SET P_NAME = REPLACE(P_DESCRIPTION, '-', '');
           SET @S=CONCAT('ALTER TABLE ',tableName,' ADD PARTITION  (PARTITION p',P_NAME,' VALUES LESS THAN ("',P_DESCRIPTION,'"))');
           SELECT @S;
           PREPARE stmt2 FROM @S;
           EXECUTE stmt2;
           DEALLOCATE PREPARE stmt2;
           SET i = i + 1 ;
    END WHILE;   
END L_END;
END $$

查看存储过程：
select `name` from mysql.proc where db = 'dnjmx' and `type` = 'PROCEDURE';

新建定时任务

CREATE EVENT dn_create_partition_nextday
ON SCHEDULE EVERY 1 DAY
DO call create_partition_nextday("dnjmx","dn");

CREATE EVENT D_dn_create_partition_nextday
ON SCHEDULE EVERY 1 DAY
DO call create_partition_nextday("dnjmx","D_dn");
与以上2条作用相同
delimiter $$
CREATE EVENT dn_create_partition_nextday
ON SCHEDULE EVERY 1 DAY
DO 
begin
call create_partition_nextday("dnjmx","dn");
call create_partition_nextday("dnjmx","D_dn");
end $$
delimiter;