新创建表设置分区:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
给已经存在的表添加分区:
alter table report_slotinfo partition by RANGE(id)
(
PARTITION p1 VALUES less than (1000000),
PARTITION p2 VALUES less than (2000000),
PARTITION p3 VALUES less than (3000000),
PARTITION p4 VALUES less than (4000000),
PARTITION p5 VALUES less than (5000000),
PARTITION p6 VALUES less than (6000000),
PARTITION p7 VALUES less than (7000000),
PARTITION p8 VALUES less than (8000000),
PARTITION p9 VALUES less than (9000000),
PARTITION p10 VALUES less than (10000000),
PARTITION p11 VALUES less than MAXVALUE
);
删除分区:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
给已经存在的表添加分区:
alter table report_slotinfo partition by RANGE(id)
(
PARTITION p1 VALUES less than (1000000),
PARTITION p2 VALUES less than (2000000),
PARTITION p3 VALUES less than (3000000),
PARTITION p4 VALUES less than (4000000),
PARTITION p5 VALUES less than (5000000),
PARTITION p6 VALUES less than (6000000),
PARTITION p7 VALUES less than (7000000),
PARTITION p8 VALUES less than (8000000),
PARTITION p9 VALUES less than (9000000),
PARTITION p10 VALUES less than (10000000),
PARTITION p11 VALUES less than MAXVALUE
);
查看某表的分区情况:
select * from INFORMATION_SCHEMA.PARTITIONS where table_name='report_slotinfo';
删除分区:
ALTER TABLE 表名 DROP PARTITION 分区名;
牛人博客: