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



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值