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) );
