MySql數據分區操作之新增分區操作。本站提示廣大學習愛好者：（MySql數據分區操作之新增分區操作）文章只能為提供參考，不一定能成為您想要的結果。以下是MySql數據分區操作之新增分區操作正文
假如想在曾經建好的表長進行分區，假如應用alter添加分區的話，mysql會提醒毛病：
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)
);
拔出測試數據
INSERT INTO tr VALUES
(1, 'desk organiser', '2003-10-15′),
(2, 'CD player', '1993-11-05′),
(3, 'TV set', '1996-03-10′),
(4, 'bookcase', '1982-01-10′),
(5, 'exercise bike', '2004-05-09′),
(6, 'sofa', '1987-06-05′),
(7, 'popcorn maker', '2001-11-22′),
(8, 'aquarium', '1992-08-04′),
(9, 'study desk', '1984-09-16′),
(10, 'lava lamp', '1998-12-25′);
查詢P2中的數據
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)
);
直接用alter table tablename add partition 方法再最初面添加分區
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
ALTER TABLE members reorganize partition p0 into (
partition m0 values less than (1960),
partition m1 values less than (1970)
);
show create table members;
CREATE TABLE `members` (
`id` int(11) DEFAULT NULL,
`fname` varchar(25) DEFAULT NULL,
`lname` varchar(25) DEFAULT NULL,
`dob` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (YEAR(dob))
(PARTITION m0 VALUES LESS THAN (1960) ENGINE = MyISAM,
PARTITION m1 VALUES LESS THAN (1970) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM) */
應用 REORGANIZE PARTITION停止數據的歸並與拆分，數據是沒有喪失的。
(具體出處參考：http://www.jb51.net/article/42544.htm)
假如用此方法在之前添加會報錯，只能用另外一種歸並拆分分區的方法操作。
