MYSQL按天自动创建分区;创建之前的分区；

本文主要是介绍MYSQL按天自动创建分区;创建之前的分区；，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

按天自动创建分区（创建当天日期后两天的）：

CREATE DEFINER=`root`@`%` PROCEDURE `P_CREATE_PARTITION_BY_DAY`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN


    #当前日期存在的分区的个数
    DECLARE ROWS_CNT INT UNSIGNED;

    #目前日期，为当前日期的后一天
    DECLARE TARGET_DATE TIMESTAMP;
    
    #分区的名称，格式为p20180620
    DECLARE PARTITIONNAME VARCHAR(9);
    
    #当前分区名称的分区值上限，即为 PARTITIONNAME + 1
    DECLARE PARTITION_ADD_DAY VARCHAR(9);

    SET TARGET_DATE = NOW() + INTERVAL 2 DAY;

    SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m%d' );

    select TO_DAYS(DATE_FORMAT( TARGET_DATE, '%Y%m%d' )) INTO PARTITION_ADD_DAY from  dual limit 1;


    SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions WHERE table_schema = IN_SCHEMANAME  COLLATE utf8_general_ci
    AND table_name = IN_TABLENAME  COLLATE utf8_general_ci
    AND partition_name = PARTITIONNAME  COLLATE utf8_general_ci
    ;
    
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
        ' ADD PARTITION (PARTITION ', PARTITIONNAME, " VALUES LESS THAN (",
                PARTITION_ADD_DAY ,") ENGINE = InnoDB);" );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
    ELSE
        SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
    END IF;
END

创建之前的分区：

CREATE DEFINER=`root`@`%` PROCEDURE `test`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN


    #当前日期存在的分区的个数
    DECLARE ROWS_CNT INT UNSIGNED;

    #目前日期，为当前日期的后一天
--     DECLARE TARGET_DATE TIMESTAMP;
    
    #分区创建开始日期
    DECLARE TARGET_DATE_START TIMESTAMP;
    
    
    #分区的名称，格式为p20180620
    DECLARE PARTITIONNAME VARCHAR(9);
    
    #当前分区名称的分区值上限，即为 PARTITIONNAME + 1
    DECLARE PARTITION_ADD_DAY VARCHAR(9);
    
    SET TARGET_DATE_START = NOW() + INTERVAL -1 YEAR;
    
    #一年前 到 今天的后两天的分区
    WHILE (TARGET_DATE_START <  (NOW()+ INTERVAL 2 DAY)) DO
            SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE_START , 'p%Y%m%d' );
            select TO_DAYS(DATE_FORMAT( TARGET_DATE_START, '%Y%m%d' )) INTO PARTITION_ADD_DAY from  dual limit 1;
            
            SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions WHERE table_schema = IN_SCHEMANAME  COLLATE utf8_general_ci
    AND table_name = IN_TABLENAME  COLLATE utf8_general_ci
    AND partition_name = PARTITIONNAME  COLLATE utf8_general_ci
    ;
    
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
        ' ADD PARTITION (PARTITION ', PARTITIONNAME, " VALUES LESS THAN (",
                PARTITION_ADD_DAY ,") ENGINE = InnoDB);" );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
    END IF;
    SET TARGET_DATE_START = TARGET_DATE_START   + INTERVAL 1 DAY;
END WHILE;
END

这篇关于MYSQL按天自动创建分区;创建之前的分区；的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！