mysql自动分区存储过程

最新推荐文章于 2023-11-09 09:39:45 发布

Alex许恒

最新推荐文章于 2023-11-09 09:39:45 发布

阅读量866

点赞数

分类专栏： Mysql

Mysql 专栏收录该内容

33 篇文章 1 订阅

订阅专栏

mysql自动分区存储过程

mysql自动分区存储过程
原创 2016年05月27日 16:28:57 1285
[sql] view plain copy
DELIMITER $$  
  
USE `dw`$$  
  
DROP PROCEDURE IF EXISTS `sp_tool_maintain_partition`$$  
  
CREATE DEFINER=`data`@`%` PROCEDURE `sp_tool_maintain_partition`(in_tabName VARCHAR(68),in_startDate DATE,in_endDate DATE)  
BEGIN  
  
    /******************************************************************  
    *   Procedure Name: sp_tool_maintain_partition  
    *   Parameter:  
    *   Creator:      
    *   Create Date:    2015-08-14  
    *   Description:    维护一个表的分区  
    *       Warning :   
    *       必须建一个最大分区pmax  
        *   /*限制了一次最多只能删除3个分区*/  
        *       仅适用于RANGE  COLUMNS(atdate)  按日分区   
    *   Version:    1.0  
    ******************************************************************/  
  
    DECLARE maxpdate DATE;  
    DECLARE minpdate DATE;  
    DECLARE pdate DATE;  
    DECLARE var_cou,i TINYINT DEFAULT 0;  
  
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  
    BEGIN  
        #获得异常信息  
        GET DIAGNOSTICS @cno = NUMBER;  
        GET DIAGNOSTICS CONDITION @cno @mysql_error = MYSQL_ERRNO,@msg_text = MESSAGE_TEXT,@sql_state = returned_sqlstate;  
        IF @mysql_error IS NOT NULL AND @msg_text IS NOT NULL THEN   
            #日志变量初始化发生异常  
            SET @log_type=0;  
            SET @msg_exception=CONCAT('error_no:',@mysql_error,' sql_state:',@sql_state,' error_text:',@msg_text);  
        END IF;  
        #存储异常信息  
        CALL sp_write_error('dw.sp_tool_maintain_partition',@sql_state,@mysql_error,@msg_text,CONCAT("in_tabName =",in_tabName ,"in_startDate=",in_startDate,'in_endDate=',in_endDate));  
    END;  
  
    first_lable:BEGIN  
  
    SET var_cou=0;  
    SELECT COUNT(*) INTO var_cou  
    FROM INFORMATION_SCHEMA.PARTITIONS   
    WHERE TABLE_SCHEMA='dw' AND `table_name`=in_tabName AND partition_name='pmax';  
  
    IF var_cou =0 THEN  
        SELECT CONCAT(in_tabName,' pmax 分区找不到,推出循环');  
        LEAVE first_lable;  
    END IF;  
  
    #获取当前最大分区和最小分区  
    SELECT DATE(MAX(RIGHT(PARTITION_NAME,8))),DATE(MIN(RIGHT(PARTITION_NAME,8))) INTO maxpdate,minpdate   
    FROM INFORMATION_SCHEMA.PARTITIONS   
    WHERE TABLE_SCHEMA='dw' AND `table_name`=in_tabName AND partition_name!='pmax';  
  
    #增加分区逻辑，in_endDate用来增加分区到指定日期  
    IF(in_endDate>maxpdate) THEN  
  
        SET pdate=maxpdate;  
        SET @sqlStat=CONCAT('ALTER TABLE ',in_tabName,' REORGANIZE PARTITION pmax INTO(');  
        WHILE pdate<in_endDate DO  
            SET pdate=DATE_ADD(pdate,INTERVAL 1 DAY);  
            SET @sqlStat=CONCAT(@sqlStat,'PARTITION p',DATE(pdate)+0,' VALUES LESS THAN (''',DATE_ADD(pdate,INTERVAL 1 DAY),'''),');  
        END WHILE;  
        SET @sqlStat=CONCAT(@sqlStat,'PARTITION pmax VALUES LESS THAN (MAXVALUE))');  
       
     PREPARE stmt FROM @sqlStat;  
     SELECT @sqlStat;  
     EXECUTE stmt;  
     DEALLOCATE PREPARE stmt;  
  
    END IF;  
  
    #删除分区逻辑，in_startDate用来删除分区到指定日期，i逻辑用来限制一次最多只删除3个分区。  
    IF(in_startDate>minpdate AND i<3) THEN  
  
        SET pdate=minpdate;  
        WHILE pdate<in_startDate DO  
            SET @sqlStat=CONCAT('ALTER TABLE ',in_tabName,' DROP PARTITION p',DATE(pdate)+0);  
            PREPARE stmt FROM @sqlStat;  
            EXECUTE stmt;  
            SELECT @sqlStat;  
            DEALLOCATE PREPARE stmt;  
            SET pdate=DATE_ADD(pdate,INTERVAL 1 DAY);  
          SET i=i+1;  
        END WHILE;  
    END IF;  
  
  END first_lable;  
END$$  
  
DELIMITER ;