范围和列表分区的管理
范围分区和列表分区的添加和删除以类似的方式处理，因此我们将在本节中讨论这两种分区的管理。
从按范围或按列表分区的表中删除分区，可以使用带有DROP PARTITION选项的ALTER TABLE语句来完成。假设你已经创建了一个按范围分区的表 ，然后使用下面的CREATE TABLE和INSERT语句填充了10条记录：
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),
-> PARTITION p4 VALUES LESS THAN (2010),
-> PARTITION p5 VALUES LESS THAN (2015)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO tr VALUES
-> (1, 'desk organiser', '2003-10-15'),
-> (2, 'alarm clock', '1997-11-05'),
-> (3, 'chair', '2009-03-10'),
-> (4, 'bookcase', '1989-01-10'),
-> (5, 'exercise bike', '2014-05-09'),
-> (6, 'sofa', '1987-06-05'),
-> (7, 'espresso maker', '2011-11-22'),
-> (8, 'aquarium', '1992-08-04'),
-> (9, 'study desk', '2006-09-16'),
-> (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
您可以看到哪些项应该插入到分区p2中，如下所示：
mysql> SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31'; +------+-------------+------------+ | id | name | purchased | +------+-------------+------------+ | 2 | alarm clock | 1997-11-05 | | 10 | lava lamp | 1998-12-25 | +------+-------------+------------+ 2 rows in set (0.00 sec)
您也可以使用分区选择来获得这些信息，如下所示：
mysql> SELECT * FROM tr PARTITION (p2); +------+-------------+------------+ | id | name | purchased | +------+-------------+------------+ | 2 | alarm clock | 1997-11-05 | | 10 | lava lamp | 1998-12-25 | +------+-------------+------------+ 2 rows in set (0.00 sec)
删除名称为“p2”的分区。
mysql> ALTER TABLE tr DROP PARTITION p2; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0
有一点非常重要，要记住，当删除一个分区时，也会删除存储在该分区中的所有数据。通过重新运行上一个SELECT查询，可以看到这一点：
mysql> SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31'; Empty set (0.00 sec)
因此，在执行ALTER TABLE …DROP PARTITION之前，用户必须对一张表具有DROP权限。
如果希望从所有分区中删除所有数据，同时保留表定义及其分区方案，请使用TRUNCATE TABLE语句。
如果您想在不丢失数据的情况下更改表的分区，请使用ALTER TABLE …REORGANIZE PARTITION。
如果你现在执行SHOW CREATE TABLE语句，你可以看到表的分区组成是如何被改变的：
mysql> SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
Table: tr
Create Table: CREATE TABLE `tr` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
1 row in set (0.00 sec)
当你将购买的列值在‘1995-01-01’到‘2004-12-31’之间的新行插入到更改后的表中时，这些行将存储在分区p3中。你可以像下面这样验证：
mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '2004-12-31'; +------+----------------+------------+ | id | name | purchased | +------+----------------+------------+ | 1 | desk organiser | 2003-10-15 | | 11 | pencil holder | 1995-07-12 | +------+----------------+------------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE tr DROP PARTITION p3; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '2004-12-31'; Empty set (0.00 sec)
由于ALTER TABLE … DROP PARTITION操作从表中删除的行数不像同级的DELETE查询那样由服务器报告。
删除列表分区使用与删除范围分区完全相同的ALTER TABLE … DROP PARTITION语法。然而，这对之后使用表的影响有一个重要的区别：你不能 再向表中插入任何包含定义删除分区的值列表中的值的行。
要向已分区的表添加新的范围分区或列表分区，请使用ALTER TABLE … ADD PARTITION语句。对于按范围分区的表，这可以用来在现有分区列 表的末尾添加一个新的范围。假设你有一个包含组织成员数据的分区表，定义如下：
mysql> CREATE TABLE members (
-> id INT,
-> fname VARCHAR(25),
-> lname VARCHAR(25),
-> dob DATE
-> )
-> PARTITION BY RANGE( YEAR(dob) ) (
-> PARTITION p0 VALUES LESS THAN (1980),
-> PARTITION p1 VALUES LESS THAN (1990),
-> PARTITION p2 VALUES LESS THAN (2000)
-> );
Query OK, 0 rows affected (0.01 sec)
进一步假设成员的最低年龄为16岁。随着日历临近2015年年底，你意识到你很快就会接纳2000年（及之后）出生的成员。你可以修改成员表以容 纳2000年到2010年出生的新成员，如下所示：
mysql> ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010)); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
对于按范围分区的表，您可以使用ADD PARTITION将新分区仅添加到分区列表的高端。试图在现有分区之间或现有分区之前以这种方式添加新分 区会导致如下错误：
mysql> ALTER TABLE members ADD PARTITION (PARTITION n VALUES LESS THAN (1970)); ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
您可以通过将第一个分区重新组织为两个新的分区来解决这个问题，这样可以将它们之间的范围分开：
mysql> ALTER TABLE members
-> REORGANIZE PARTITION p0 INTO (
-> PARTITION n0 VALUES LESS THAN (1970),
-> PARTITION n1 VALUES LESS THAN (1980)
-> );
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
使用SHOW CREATE TABLE可以看到ALTER TABLE语句达到了预期效果：
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=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)
你也可以使用ALTER TABLE … ADD PARTITION向按列表分区的表中添加新分区。假设表tt是用下面的CREATE table语句定义的：
mysql> 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)
-> );
Query OK, 0 rows affected (0.01 sec)
您可以添加一个新的分区，其中存储数据列值为7,14和21的行，如下所示：
mysql> ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21)); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0
请记住，不能添加包含现有分区的值列表中已经包含的任何值的新LIST分区。如果你尝试这样做，将会产生一个错误：
mysql> ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8, 12)); ERROR 1495 (HY000): Multiple definition of same constant in list partitioning
因为数据列值为12的任何行都已经分配给了分区p1，所以不能在表tt上创建一个包含12的新分区。为此，可以删除p1，添加np，再创建一个定义 有所修改的p1。但是，如前所述，这将导致存储在p1中的所有数据丢失，而通常情况下这并不是我们真正想要的。另一种解决方案可能是在新的 分区下复制表，然后使用CREATE TABLE … SELECT …，然后删除旧表并重命名新表，但在处理大量数据时，这可能非常耗时。在要求高可用 性的情况下，这可能也是不可行的。
您可以在单个ALTER TABLE中添加多个分区。ADD PARTITION语句如下所示：
mysql> CREATE TABLE employees (
-> id INT NOT NULL,
-> fname VARCHAR(50) NOT NULL,
-> lname VARCHAR(50) NOT NULL,
-> hired DATE NOT NULL
-> )
-> PARTITION BY RANGE( YEAR(hired) ) (
-> PARTITION p1 VALUES LESS THAN (1991),
-> PARTITION p2 VALUES LESS THAN (1996),
-> PARTITION p3 VALUES LESS THAN (2001),
-> PARTITION p4 VALUES LESS THAN (2005)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> ALTER TABLE employees ADD PARTITION (
-> PARTITION p5 VALUES LESS THAN (2010),
-> PARTITION p6 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
幸运的是，MySQL的分区实现提供了重新定义分区而不会丢失数据的方法。我们首先看几个涉及范围分区的简单例子。回想一下成员表的定义， 如下所示：
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=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)
假设您想将表示1960年以前出生的成员的所有行移动到一个单独的分区中。我们已经看到，这不能使用ALTER TABLE … ADD PARTITION添加分 区。不过，用户可以使用另一个与分区相关的扩展ALTER TABLE来实现这一点：
mysql> ALTER TABLE members REORGANIZE PARTITION n0 INTO (
-> PARTITION s0 VALUES LESS THAN (1960),
-> PARTITION s1 VALUES LESS THAN (1970)
-> );
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
实际上，该命令将分区p0分成两个新的分区s0和s1。它还根据PARTITION …VALUES …子句所体现的规则，将存储在p0中的数据移动到新的分 区中。，使得s0只包含年份（dob）小于1960的那些记录，s1包含年份（dob）大于或等于1960但小于1970的那些行。
REORGANIZE PARTITION子句也可以用于合并相邻的分区。你可以将前面的语句对members表的影响颠倒过来，如下所示：
mysql> ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
-> PARTITION p0 VALUES LESS THAN (1970)
-> );
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
使用REORGANIZE PARTITION拆分或合并分区时不会丢失数据。在执行上述语句时，MySQL将存储在分区50和s1中的所有记录移动到分区p0中。
REORGANIZE PARTITION的通用语法如下所示：
ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions);
这里，tbl_name是分区表的名称，而partition_list是一个以逗号分隔的列表，其中包含一个或多个要更改的现有分区的名称。 partition_definitions是一个逗号分隔的新分区定义列表，它遵循与CREATE TABLE中使用的partition_definitions列表相同的规则。在使用 REORGANIZE PARTITION时，您并不局限于将几个分区合并为一个分区，或者将一个分区拆分为多个分区。例如，您可以将members表的所有四个 分区重组为两个，如下所示：
mysql> ALTER TABLE members REORGANIZE PARTITION p0,n1,p1,p2 INTO (
-> PARTITION m0 VALUES LESS THAN (1980),
-> PARTITION m1 VALUES LESS THAN (2000)
-> );
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
您还可以对通过LIST进行分区的表使用REORGANIZE PARTITION。让我们回到向列表分区tt表添加新分区并失败的问题，因为新分区的值已经存在 于一个现有分区的值列表中。我们可以通过添加一个只包含非冲突值的分区来处理这个问题，然后重新组织新分区和现有分区，以便存储在现有 分区中的值现在移动到新分区中：
mysql> ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE tt REORGANIZE PARTITION p2,np INTO (
-> PARTITION p2 VALUES IN (7, 14),
-> PARTITION np VALUES in (4, 8, 21)
-> );
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
以下是使用ALTER TABLE…REORGANIZE PARTITION对按范围或列表分区的表进行重新分区时需要注意的一些关键点:
.用于确定新分区方案的PARTITION选项遵循与CREATE TABLE语句相同的规则。
一个新的范围分区方案不能有任何重叠的范围；新的列表分区方案不能有任何重叠的值集。
.partition_definitions列表中的分区组合总体上应该与partition_list中命名的组合分区的范围或值集相同。
例如，分区p1和p2一起覆盖了成员表中的1980年到1999年。这两个分区的任何重组都应该覆盖相同的年份范围。
.对于按范围分区的表，可以只重组相邻的分区；您不能跳过范围分区。
例如，不能使用以ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO …开头的语句来重组members表。因为p0包含1970年之前的年份， 而p2包含1990年到1999年的年份，所以它们不是相邻的分区。（在这种情况下不能跳过分区p1。）
.不能使用REORGANIZE PARTITION分区来更改表使用的分区类型；例如，您不能将范围分区更改为散列分区或相反。也不能使用此语句更改分区 表达式或列。要在不删除和重新创建表的情况下完成上述任务，可以使用ALTER TABLE … PARTITION BY …,如下所示：
mysql> ALTER TABLE members
-> PARTITION BY HASH( YEAR(dob) )
-> PARTITIONS 8;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0