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 );
2. 为现有表创建分区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 分区名;
4. 追加表分区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);
5. 查看表分区信息select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_name='表名';
6. 查看查询语句涉及分区信息explain partitions select … from 表名 where …;
bitscn.com