• Mysql分区表及自动创建分区Partition


    Range分区表建表语句如下,其中分区键必须和id构成主键和唯一键

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    CREATE TABLE `test1` (
      `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '自增主键(guid)',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `partition_key` int(8) NOT NULL COMMENT '分区键(格式:yyyyMMdd)',
      PRIMARY KEY (`id`,`partition_key`),
      UNIQUE KEY `id_UNIQUE` (`id`,`partition_key`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    PARTITION BY RANGE (partition_key)
    (PARTITION p0 VALUES LESS THAN (20180619) ENGINE = InnoDB,
     PARTITION p20180619 VALUES LESS THAN (20180620) ENGINE = InnoDB,
     PARTITION p20180621 VALUES LESS THAN (20180622) ENGINE = InnoDB,
     PARTITION p20180622 VALUES LESS THAN (20180623) ENGINE = InnoDB,
     PARTITION p20180623 VALUES LESS THAN (20180624) ENGINE = InnoDB); 

    新增分区

    1
    2
    3
    alter TABLE `test1` add PARTITION(
     PARTITION p20180629 VALUES LESS THAN (20180630) ENGINE = InnoDB
    );

    删除分区

    1
    alter table `test1`  drop PARTITION p20180629;

    Mysql不能自动创建分区,需要使用mysql event事件的方式自动创建分区

    1.创建分区的存储过程如下(每次执行先校验当前分区是否存在,如果存在则不处理;不存在则创建):

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    DELIMITER $$
    #该表所在数据库名称
    USE `demo`$$
    DROP PROCEDURE IF EXISTS `create_partition_by_day`$$
    CREATE PROCEDURE `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 1 DAY;
        SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m%d' );
        SET TARGET_DATE = TARGET_DATE + INTERVAL 1 DAY;
        SET PARTITION_ADD_DAY = DATE_FORMAT( TARGET_DATE, '%Y%m%d' );
        SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions
        WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
        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$$
    DELIMITER ;

    2.数据库定时任务(每小时执行一次)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    DELIMITER $$
    #该表所在的数据库名称
    USE `demo`$$
    CREATE EVENT IF NOT EXISTS `daily_generate_partition`
    ON SCHEDULE EVERY 1 hour   #执行周期,还有天、月等等
    STARTS '2018-06-20 00:00:00'
    ON COMPLETION PRESERVE
    ENABLE
    COMMENT 'Creating partitions'
    DO BEGIN
        #调用刚才创建的存储过程,第一个参数是数据库名称,第二个参数是表名称
        CALL datacollectcenter.create_partition_by_day('demo','test1');
    END$$
    DELIMITER ;
  • 相关阅读:
    文件批量上传
    XML如何不进行解析原样输出
    1456710017
    java标准百分比
    解决SSM项目下静态资源(img、js、css)无法引用的问题
    MySQL查询当天数据以及大量查询时提升速度
    多线程test
    JAVA中等待所有线程都执行结束(转2)
    java中等待所有线程都执行结束
    关于Java多线程(JAVA多线程实现的四种方式)
  • 原文地址:https://www.cnblogs.com/webenh/p/11667778.html
Copyright © 2020-2023  润新知