mysql分区语录_MYSQL分区管理

MySQL 5.7提供了一系列的方法去修改分区表，有添加、删除、重定义、合并或拆分分区等，所有这些行为都可以用ALTER TABLE语句来实现，也有多种方法获取有关分区表或分区的信息。

修改表的分区方案，必须用带partition_options语法的alter table语句，一般用PARTITION BY。如分区表trb3：

CREATE TABLE trb3 (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)

);

修改为：sql ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;

ALTER TABLE ... ENGINE = ...仅仅会修改表的存储引擎而保持分区方案完好无损。ALTER TABLE ... REMOVE PARTITIONING则删除表的分区。

仅PARTITION BY, ADD PARTITION, DROP PARTITION, REORGANIZE PARTITION, or COALESCE PARTITION短语用于ALTER TABLE语句，如果你要删除一个分区并重新组织剩下的分区，则你必须执行两步单独的ALTER TABLE语句，一个带drop partition，另一个带reorganize partitions。

使用ALTER TABLE ... TRUNCATE PARTITION语句删除选定的分区的所有行。

LIST和RANGE分区管理

删除p2分区：

mysql> ALTER TABLE tr DROP PARTITION p2;

Query OK, 0 rows affected (0.03 sec)

如果你删除了一个分区，是你也删除了该分区里的所有数据。所以你必须有删除权限。

如果您希望从所有分区删除所有数据，同时保留表的定义和划分方案，使用TRUNCATE TABLE语句

如果你想要修改表的分区而不丢失数据，则使用alter table ... reorganize partition代替。

删除LIST分区和删除RANGE分区一样使用alter table ... drop partition语法，但有一点不同的是：你不能再向删除的分区中插入相应的数据了。

给分区表添加新的分区，则使用alter table ... add partition语句，针对RANGE分区表，你可以添加新分区在已有分区后面。

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 members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

尝试在已有分区之前或之间添加新分区，则会出错：

mysql> ALTER TABLE members

> ADD PARTITION (

> PARTITION n VALUES LESS THAN (1960));

ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »

increasing for each partition

不过你可以重新组织第一个分区为两个新的分区，如：

ALTER TABLE members

REORGANIZE PARTITION p0 INTO (

PARTITION n0 VALUES LESS THAN (1960),

PARTITION n1 VALUES LESS THAN (1970)

);

你也可以添加新分区到LIST分区表上：

CREATE TABLE tt (

id INT,

data INT

)

PARTITION BY LIST(data) (

PARTITION p0 VALUES IN (5, 10, 15),

PARTITION p1 VALUES IN (6, 12, 18)

);

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

注意这里新添加的分区里不能包含已在其他分区里的列值。如：

mysql> ALTER TABLE tt ADD PARTITION

> (PARTITION np VALUES IN (4, 8, 12));

ERROR 1465 (HY000): Multiple definition of same constant »

in list partitioning

你也可以添加多个分区在一个ALTER TABLE ... ADD PARTITION语句中：

ALTER TABLE employees ADD PARTITION (

PARTITION p5 VALUES LESS THAN (2010),

PARTITION p6 VALUES LESS THAN MAXVALUE

);

幸运的是，你可以重新定义分区方案也不丢失数据，如下所示的RANGE分区表：

mysql> SHOW CREATE TABLE members\G

*************************** 1. row ***************************

Table: members

Create Table: 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=latin1

PARTITION BY RANGE (