mysql partition 功能

show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
//YES 表示开启

drop table if exists `netingcn_com`;
create table `netingcn_com` (
  `id` int(11) unsigned not null auto_increment,
  `day` int(11) not null default 0,
  primary key (`id`, `day`)
) engine=innodb default charset=utf8 auto_increment=1;
alter table `netingcn_com` partition by range(`day`) (
  partition p_2012 values less than (20130000),
  partition p_2013 values less than (20140000)
);
或
drop table if exists `netingcn_com`;
create table `netingcn_com` (
  `id` int(11) unsigned not null auto_increment,
  `day` int(11) not null default 0,
  primary key (`id`, `day`)
) engine=innodb default charset=utf8 auto_increment=1
 partition by range(`day`) (
  partition p_2012 values less than (20130000),
  partition p_2013 values less than (20140000)
);
可以explain命令查看分区是否创建成功
explain partitions select * from netingcn_com where day = 20130412;
+----+-------------+--------------+------------+-------+
| id | select_type | table        | partitions | type  |
+----+-------------+--------------+------------+-------+
|  1 | SIMPLE      | netingcn_com | p_2013     | index |
+----+-------------+--------------+------------+-------+

增加分区
alter table netingcn_com add partition (
  partition p_2014 values less than (20150000)
);
删除分区
alter table netingcn_com drop partition p_2012;

alter table netingcn_com reorganize partition p_2013,p_2014 into (partition p_2014 values less than (20150000));