《MYSQL數據庫MySql數據分區(qū)操作之新增分區(qū)操作》要點：
本文介紹了MYSQL數據庫MySql數據分區(qū)操作之新增分區(qū)操作，希望對您有用。如果有疑問，可以聯系我們。
如果想在已經建好的表上進行分區(qū),如果使用alter添加分區(qū)的話,mysql會提示差錯：
MYSQL實例
代碼如下:
ERROR 1505 <HY000> Partition management on a not partitioned table is not possible
正確的辦法是新建一個具有分區(qū)的表,結構一致,然后用insert into 分區(qū)表 select * from 原始表;
測試創(chuàng)建分區(qū)表文件MYSQL實例
代碼如下:
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分區(qū)的同時,也會將其下的所有數據刪除
代碼如下:
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′;
創(chuàng)立一個新的測試表
代碼如下:
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 方式再最后面添加分區(qū)
代碼如下:
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進行數據的合并與拆分,數據是沒有丟失的.
(詳細出處參考：/article/42544.htm)
如果用此方式在之前添加會報錯,只能用另一種合并拆分分區(qū)的方式操作.
維易PHP培訓學院每天發(fā)布《MYSQL數據庫MySql數據分區(qū)操作之新增分區(qū)操作》等實戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/11533.html
