mysql与oracle数据库创建partition分区脚本

mysql和oracle数据库分区分为range，list，hash等多种分区方式，在这里我主要对根据时间创建partition分区脚本进行介绍：

根据时间进行分区的为range分区方式：

mysql创建range分区：

DROP TABLE IF EXISTS test;  
CREATE TABLE test (  
  id int(10) unsigned NOT NULL AUTO_INCREMENT,  
  time datetime NOT NULL COMMENT '业务时间',  
  PRIMARY KEY (id, time)  
)
PARTITION BY RANGE (TO_DAYS(time))  
(PARTITION part_201501 VALUES LESS THAN (TO_DAYS('2015-02-01'))ENGINE = InnoDB,  
 PARTITION part_201502 VALUES LESS THAN (TO_DAYS('2015-03-01'))ENGINE = InnoDB,  
 PARTITION part_201503 VALUES LESS THAN (TO_DAYS('2015-04-01'))ENGINE = InnoDB,  
 PARTITION part_201504 VALUES LESS THAN (TO_DAYS('2015-05-01'))ENGINE = InnoDB,  
 PARTITION part_201505 VALUES LESS THAN (TO_DAYS('2015-06-01'))ENGINE = InnoDB,  
 PARTITION part_201506 VALUES LESS THAN (TO_DAYS('2015-07-01'))ENGINE = InnoDB) ;  

mysql添加分区

alter table user add partition(partition p4 values less than MAXVALUE);

mysql删除分区

ALTER TABLE users DROP PARTITION p0;

oracle创建时间range分区

create table pdba (id number, time date) partition by range (time)
(
partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),
partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),
partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),
partition p4 values less than (maxvalue)
)

oracle添加分区

ALTER TABLE partition_test ADD PARTITION P5 VALUES LESS THAN(TO_DATE('2018-01-01','YYYY-MM-DD'));

oracle删除分区

ALTER TABLE partition_test DROP PARTITION P4;