如果想在已經(jīng)建好的表上進(jìn)行分區(qū)，如果使用alter添加分區(qū)的話，mysql會(huì)提示錯(cuò)誤：

代碼如下:

ERROR 1505 <HY000> Partition management on a not partitioned table is not possible

正確的方法是新建一個(gè)具有分區(qū)的表，結(jié)構(gòu)一致，然后用insert into 分區(qū)表 select * from 原始表;

測(cè)試創(chuàng)建分區(qū)表文件

代碼如下:

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)

);

插入測(cè)試數(shù)據(jù)

代碼如下:

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中的數(shù)據(jù)

代碼如下:

select * from tr where purchased between '1995-01-01′ and '2004-12-31′;

如果刪除P2，在刪除P2分區(qū)的同時(shí)，也會(huì)將其下的所有數(shù)據(jù)刪除

代碼如下:

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) */

再次插入數(shù)據(jù)時(shí)，會(huì)將原P2的數(shù)據(jù)插入至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)建一個(gè)新的測(cè)試表

代碼如下:

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

復(fù)制代碼 代碼如下:

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) */

更多信息請(qǐng)查看IT技術(shù)專欄