CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
);
删除分区(需要DROP权限)
ALTER TABLE trb3 DROP PARTITION p2;
增加分区,对于RANGE分区,只能从分区列表的最高端开始增加。
CREATE TABLE members (
id INT,
fname VARCHAR(25),
lname VARCHAR(25),
dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION p0 VALUES LESS THAN (1970),
PARTITION p1 VALUES LESS THAN (1980),
PARTITION p2 VALUES LESS THAN (1990)
);
#增加一个分区。
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
#增加多个分区
ALTER TABLE members ADD PARTITION (
PARTITION p5 VALUES LESS THAN (2010),
PARTITION p6 VALUES LESS THAN MAXVALUE
);
# 增加的分区必须从最高端开始增加,以下增加会报错:
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (1960));