如果想在已经建好的表上进行分区,如果使用alter添加分区的话,mysql会提示错误:
ERROR 1505 <HY000> Partition management on a not partitioned table is not possible
正确的方法是新建一个具有分区的表,结构一致,然后用
insert into 分区表 select * from 原始表;
测试添加分区和删除分区
添加删除range分区
(1)创建一个分区:
CREATE TABLE titles (emp_no INT NOT NULL,title VARCHAR(50) NOT NULL,from_date DATE NOT NULL,to_date DATE,KEY (emp_no),PRIMARY KEY (emp_no,title, from_date)
) partition by range columns(from_date)
(partition p01 values less than ('1985-12-31'),
partition p02 values less than ('1990-12-31'),
partition p03 values less than ('1995-12-31'),
partition p04 values less than ('2000-12-31'),
partition p05 values less than ('2005-12-31'),
partition p06 values less than ('2010-12-31'),
partition p07 values less than ('2015-12-31'),
partition p08 values less than ('2020-12-31'),
partition p09 values less than ('2025-12-31'),
partition p10 values less than ('2030-12-31')
);
(2)添加分区:
备注:不能超过p04的范围,严格递增每个分区,即最小不能小于前一个分区
下面新曾了两个分区n01和n02
alter table titlesreorganize partition p04 into(partition n01 values less than('1997-12-31'),partition n02 values less than('1998-12-31'),partition p04 values less than('2000-12-31')
);
(3)删除分区:
删除分区即删除数据。
alter table titles drop partition p01;