mysql 开启分区_Mysql实现分区功能（一）

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 …;