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