ERROR 1505 <HY000> Partition management on a not partitioned table is not possible
正确的方法是新建一个具有分区的表，结构一致，然后用insert into 分区表 select * from 原始表;
测试创建分区表文件
复制代码 代码如下:
CREATE TABLE tr (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) );
select * from tr where purchased between '1995-01-01′ and '2004-12-31′;
如果删除P2，在删除P2分区的同时，也会将其下的所有数据删除
复制代码 代码如下:
alter table tr drop partition p2; show create table tr; CREATE TABLE `tr` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `purchased` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (YEAR(purchased)) (PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM) */
再次插入数据时，会将原P2的数据插入至P3中
复制代码 代码如下:
INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12′); ALTER TABLE tr DROP PARTITION p3; SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01′ AND '2004-12-31′;
创建一个新的测试表
复制代码 代码如下:
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) );
