bitsCN.com
1. 创建分区表
CREATE TABLE `表名` ( `EQUIPMENTID` char(17) NOT NULL, `ATTRIBUTEID` char(4) NOT NULL, `VALUE` varchar(20) NOT NULL, `COLLECTTIME` datetime NOT NULL ) ENGINE=InnoDB (适用大部分引擎,可根据需要调整) DEFAULT CHARSET=gbk(编码可根据需要修改) PARTITION BY RANGE (to_days(COLLECTTIME)) (PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')), PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) , PARTITION p201002 VALUES LESS THAN (to_days('2010-03-01')) , PARTITION p201003 VALUES LESS THAN (to_days('2010-04-01')) , PARTITION p201004 VALUES LESS THAN (to_days('2010-05-01')) , PARTITION p201005 VALUES LESS THAN (to_days('2010-06-01')) , PARTITION p201006 VALUES LESS THAN (to_days('2010-07-01')) , PARTITION p201007 VALUES LESS THAN (to_days('2010-08-01')) , PARTITION p201008 VALUES LESS THAN (to_days('2010-09-01')) , PARTITION p201009 VALUES LESS THAN (to_days('2010-10-01')) , PARTITION p201010 VALUES LESS THAN (to_days('2010-11-01')), PARTITION p201011 VALUES LESS THAN (to_days('2010-12-01')), PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')), PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')), PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')), PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')), PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')), PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')), PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')), PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')), PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')), PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')), PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')), PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')), PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')), PARTITION pmax VALUES LESS THAN MAXVALUE );
alter table 表名 PARTITION BY RANGE (to_days(COLLECTTIME)) (PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')), PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) , PARTITION p201002 VALUES LESS THAN (to_days('2010-03-01')) , PARTITION p201003 VALUES LESS THAN (to_days('2010-04-01')) , PARTITION p201004 VALUES LESS THAN (to_days('2010-05-01')) , PARTITION p201005 VALUES LESS THAN (to_days('2010-06-01')) , PARTITION p201006 VALUES LESS THAN (to_days('2010-07-01')) , PARTITION p201007 VALUES LESS THAN (to_days('2010-08-01')) , PARTITION p201008 VALUES LESS THAN (to_days('2010-09-01')) , PARTITION p201009 VALUES LESS THAN (to_days('2010-10-01')) , PARTITION p201010 VALUES LESS THAN (to_days('2010-11-01')), PARTITION p201011 VALUES LESS THAN (to_days('2010-12-01')), PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')), PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')), PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')), PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')), PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')), PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')), PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')), PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')), PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')), PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')), PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')), PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')), PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')), PARTITION pmax VALUES LESS THAN MAXVALUE );
3. 删除表中的指定分区
ALTER TABLE 表名 DROP PARTITION 分区名;
ALTER TABLE 表名 DROP PARTITION pmax; ALTER TABLE 表名 ADD PARTITION ( PARTITION p201201 VALUES LESS THAN (to_days('2012-2-1')), PARTITION pmax VALUES LESS THAN MAXVALUE);
SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME='表名';
explain partitions select … from 表名 where …;
