Mysql 表分區

標籤：add   tab   style   inf   des   esc   添加分區   alter   最大   

建立分區表：

 DELIMITER // create table VMMoniterData (    id_ bigint not null AUTO_INCREMENT,    vmid varchar(75) null,    cpu varchar(75) null,    memory varchar(75) null,    bpsRead varchar(75) null,    bpsWrite varchar(75) null,    intranetRX0 varchar(75) null,    intranetRX1 varchar(75) null,    intranetWX0 varchar(75) null,    intranetWX1 varchar(75) null,    timeStamp datetime null,    PRIMARY KEY (id_,timeStamp)) engine InnoDBPARTITION BY RANGE (TO_DAYS(timeStamp) ) (    PARTITION p1 VALUES LESS THAN ( TO_DAYS(‘20161101‘) ),    PARTITION p2 VALUES LESS THAN ( TO_DAYS(‘20161102‘) ),    PARTITION p3 VALUES LESS THAN ( TO_DAYS(‘20161103‘) ),    PARTITION p4 VALUES LESS THAN ( TO_DAYS(‘20161104‘) ),    PARTITION p5 VALUES LESS THAN ( TO_DAYS(‘20161105‘) ),    PARTITION p6 VALUES LESS THAN ( TO_DAYS(‘20161106‘) ),    PARTITION p7 VALUES LESS THAN ( TO_DAYS(‘20161107‘) ),    PARTITION p8 VALUES LESS THAN ( TO_DAYS(‘20161108‘) ),    PARTITION p9 VALUES LESS THAN ( TO_DAYS(‘20161109‘) ),    PARTITION p10 VALUES LESS THAN ( TO_DAYS(‘20161110‘) ));//DELIMITER ;

添加分區表：

ALTER TABLE VMMoniterData ADD PARTITION(PARTITION p315 VALUES LESS THAN (TO_DAYS(‘22190527‘)));

查看錶分區情況：

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

添加分區最大值

ALTER TABLE VMMoniterData ADD PARTITION (PARTITION p11 VALUES LESS THAN maxvalue);

每天自動添加分區

 

Mysql 表分區