mysql分区表简单例子

mysql分表可以有三种办法实现：

1.直接物理表实现分区 （主要由程序控制）；

2.通过merge存储引擎

3.通过分区字段进行分区

当然也可以通过上面3种方式进行自由组合

下面的例子说的是第三种-分区表：

 a--创建分区表

create table test_log
(
  id bigint(20) not null AUTO_INCREMENT,
  name varchar(20) null,
  gmt_create datetime not null,
  PRIMARY KEY (id,gmt_create),
  key idx_name (name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by range(to_days(gmt_create))
(
partition p20141115 values less than (to_days('2014-11-15')),
partition p20141130 values less than (to_days('2014-11-30')),
partition p20141215 values less than (to_days('2014-12-15')),
partition p20141231 values less than (to_days('2014-12-31')),
partition p20150115 values less than (to_days('2015-01-15')),
partition p20150131 values less than (to_days('2015-01-31')),
partition p20150215 values less than (to_days('2015-02-15')),
partition p20150228 values less than (to_days('2015-02-28')),
partition p20150315 values less than (to_days('2015-03-15')),
partition p20150331 values less than (to_days('2015-03-31')),
partition p20150415 values less than (to_days('2015-04-15')),
partition p20150430 values less than (to_days('2015-04-30'))
); 


b--添加分区
alter table test_log add partition (partition p20150515 values less than (to_days('2015-05-15')));
alter table test_log add partition (partition p20150531 values less than (to_days('2015-05-31')));
alter table test_log add partition (partition p20150615 values less than (to_days('2015-06-15')));
alter table test_log add partition (partition p20150630 values less than (to_days('2015-06-30')));
alter table test_log add partition (partition p20150715 values less than (to_days('2015-07-15')));
alter table test_log add partition (partition p20150731 values less than (to_days('2015-07-31')));
alter table test_log add partition (partition p20150815 values less than (to_days('2015-08-15')));
alter table test_log add partition (partition p20150831 values less than (to_days('2015-08-31')));
alter table test_log add partition (partition p20150915 values less than (to_days('2015-09-15')));
alter table test_log add partition (partition p20150930 values less than (to_days('2015-09-30')));
alter table test_log add partition (partition p20151015 values less than (to_days('2015-10-15')));


c--删除分区
alter table test_log drop partition p20150515;


d--插入数据
insert into test_log
select null,'jack',now()


e-- 指定分区查询

select * from test_log partition(p20150930);