范围（RANGE）和列表（LIST）分区的添加和删除处理方式类似，因此本节讨论两种分区的管理。有关处理按哈希（HASH）或键（KEY）分区的表的信息，请参见 第 26.3.2 节，“HASH 和 KEY 分区的管理”。Adding and dropping of range and list partitions are handled in a similar fashion, so we discuss the management of both sorts of partitioning in this section. For information about working with tables that are partitioned by hash or key, see Section 26.3.2, “Management of HASH and KEY Partitions”.

从按 RANGE 或 LIST 分区的表中删除分区，可以使用带有 ALTER TABLE 选项的 DROP PARTITION 语句来完成。假设您创建了一个按范围分区的表，然后使用以下 CREATE TABLE 和 INSERT 语句插入了 10 条记录：Dropping a partition from a table that is partitioned by either RANGE or by LIST can be accomplished using the ALTER TABLE statement with the DROP PARTITION option. Suppose that you have created a table that is partitioned by range and then populated with 10 records using the following CREATE TABLE and INSERT statements:

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.28 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.05 sec)
Records: 10  Duplicates: 0  Warnings: 0

您可以查看哪些项目应已插入到分区 p2，如这里所示：You can see which items should have been inserted into partition p2 as shown here:

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)

你也可以使用分区选择来获取这些信息，如下所示：You can also get this information using partition selection, as shown here:

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)

更多信息请参见第 26.5 节，“分区选择”。See Section 26.5, “Partition Selection”, for more information.

要删除名为p2的分区，请执行以下命令：To drop the partition named p2, execute the following command:

mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)

非常重要的一点是，当你删除一个分区时，你也会删除该分区中存储的所有数据。你可以通过重新运行之前的 SELECT 查询来看到这是事实：It is very important to remember that, when you drop a partition, you also delete all the data that was stored in that partition. You can see that this is the case by re-running the previous SELECT query:

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)

因此，您必须拥有该表的 DROP 权限，才能在该表上执行 ALTER TABLE ... DROP PARTITION 操作。Because of this, you must have the DROP privilege for a table before you can execute ALTER TABLE ... DROP PARTITION on that table.

如果您希望在保留表定义及其分区方案的同时删除所有分区中的所有数据，请使用 TRUNCATE TABLE 语句。（参见 第 15.1.37 节，“TRUNCATE TABLE 语句”。）If you wish to drop all data from all partitions while preserving the table definition and its partitioning scheme, use the TRUNCATE TABLE statement. (See Section 15.1.37, “TRUNCATE TABLE Statement”.)

If you intend to change the partitioning of a table without losing data, use ALTER TABLE ... REORGANIZE PARTITION instead. See below or in 此语句删除名为, for information about REORGANIZE PARTITION.If you intend to change the partitioning of a table without losing data, use ALTER TABLE ... REORGANIZE PARTITION instead. See below or in Section 15.1.9, “ALTER TABLE Statement”, for information about REORGANIZE PARTITION.

如果您现在执行 SHOW CREATE TABLE 语句，您可以看到表的分区结构已经发生了变化：If you now execute a SHOW CREATE TABLE statement, you can see how the partitioning makeup of the table has been changed:

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 COLLATE=utf8mb4_0900_ai_ci
/*!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)

当你向更改后的表中插入 purchased 列的值在 '1995-01-01' 和 '2004-12-31'（含）之间的新行时，这些行会存储在分区 p3 中。你可以通过以下方式验证：When you insert new rows into the changed table with purchased column values between '1995-01-01' and '2004-12-31' inclusive, those rows are stored in partition p3. You can verify this as follows:

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.03 sec)

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '2004-12-31';
Empty set (0.00 sec)

由于 ALTER TABLE ... DROP PARTITION 而从表中删除的行数，服务器不会像使用等效的 DELETE 查询那样进行报告。The number of rows dropped from the table as a result of ALTER TABLE ... DROP PARTITION is not reported by the server as it would be by the equivalent DELETE query.

删除 LIST 分区使用的 ALTER TABLE ... DROP PARTITION 语法与删除 RANGE 分区完全相同。然而，这对你之后使用表的影响有一个重要区别：你不能再向表中插入任何包含已删除分区定义的值列表中的值的行。（有关示例，请参见 第 26.2.2 节，“LIST 分区”。）Dropping LIST partitions uses exactly the same ALTER TABLE ... DROP PARTITION syntax as used for dropping RANGE partitions. However, there is one important difference in the effect this has on your use of the table afterward: You can no longer insert into the table any rows having any of the values that were included in the value list defining the deleted partition. (See Section 26.2.2, “LIST Partitioning”, for an example.)

要向先前已分区的表添加新的范围或列表分区，请使用 ALTER TABLE ... ADD PARTITION 语句。对于按 RANGE 分区的表，这可以用于在现有分区列表的末尾添加一个新的范围。假设你有一个包含你组织会员数据的分区表，其定义如下：To add a new range or list partition to a previously partitioned table, use the ALTER TABLE ... ADD PARTITION statement. For tables which are partitioned by RANGE, this can be used to add a new range to the end of the list of existing partitions. Suppose that you have a partitioned table containing membership data for your organization, which is defined as follows:

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

进一步假设会员的最低年龄为 16 岁。随着 2015 年底的临近，你意识到必须尽快准备好接纳 2000 年（及以后）出生的会员。你可以按如下方式修改 members 表，以容纳 2000 年至 2010 年出生的新会员：Suppose further that the minimum age for members is 16. As the calendar approaches the end of 2015, you realize that you must soon be prepared to admit members who were born in 2000 (and later). You can modify the members table to accommodate new members born in the years 2000 to 2010 as shown here:

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

对于按范围分区的表，你只能使用ADD PARTITION向分区列表的高端添加新分区。尝试以这种方式在现有分区之间或之前添加新分区会导致如下所示的错误：With tables that are partitioned by range, you can use ADD PARTITION to add new partitions to the high end of the partitions list only. Trying to add a new partition in this manner between or before existing partitions results in an error as shown here:

mysql> ALTER TABLE members
     >     ADD PARTITION (
     >     PARTITION n VALUES LESS THAN (1970));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
   increasing for each partition

你可以通过将第一个分区重新组织为两个新分区来解决这个问题，这样可以在它们之间拆分范围，如下所示：You can work around this problem by reorganizing the first partition into two new ones that split the range between them, like this:

ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1970),
        PARTITION n1 VALUES LESS THAN (1980)
);

使用SHOW CREATE TABLE，你可以看到ALTER TABLE语句已达到预期效果：Using SHOW CREATE TABLE you can see that the ALTER TABLE statement has had the desired effect:

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 COLLATE=utf8mb4_0900_ai_ci
/*!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)

另请参见第 15.1.9.1 节，“ALTER TABLE 分区操作”。See also Section 15.1.9.1, “ALTER TABLE Partition Operations”.

您还可以使用 ALTER TABLE ... ADD PARTITION 向按 LIST 分区的表添加新分区。假设表 tt 是使用以下 CREATE TABLE 语句定义的：You can also use ALTER TABLE ... ADD PARTITION to add new partitions to a table that is partitioned by LIST. Suppose a table tt is defined using the following CREATE TABLE statement:

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

您可以添加一个新分区来存储 data 列值为 7、14 和 21 的行，如下所示：You can add a new partition in which to store rows having the data column values 7, 14, and 21 as shown:

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

请记住，您 不能 添加包含现有分区值列表中已有值的新 LIST 分区。如果尝试这样做，将会导致错误：Keep in mind that you cannot add a new LIST partition encompassing any values that are already included in the value list of an existing partition. If you attempt to do so, an error results:

mysql> ALTER TABLE tt ADD PARTITION 
     >     (PARTITION np VALUES IN (4, 8, 12));
ERROR 1465 (HY000): Multiple definition of same constant »
                    in list partitioning

因为任何 data 列值为 12 的行已经被分配到分区 p1，所以你不能在表 tt 上创建一个包含 12 值的新分区。为实现这一点，你可以删除 p1，然后添加 np，再创建一个定义经过修改的新 p1。然而，如之前讨论的那样，这将导致 p1 中所有数据的丢失——而通常情况下，这并不是你真正想做的。另一种解决方案可能是通过新的分区将表复制一份，并使用 CREATE TABLE ... SELECT ... 将数据复制进去，然后删除旧表并重命名新表，但在处理大量数据时，这可能非常耗时。在需要高可用性的情况下，这也可能不可行。Because any rows with the data column value 12 have already been assigned to partition p1, you cannot create a new partition on table tt that includes 12 in its value list. To accomplish this, you could drop p1, and add np and then a new p1 with a modified definition. However, as discussed earlier, this would result in the loss of all data stored in p1—and it is often the case that this is not what you really want to do. Another solution might appear to be to make a copy of the table with the new partitioning and to copy the data into it using CREATE TABLE ... SELECT ..., then drop the old table and rename the new one, but this could be very time-consuming when dealing with a large amounts of data. This also might not be feasible in situations where high availability is a requirement.

您可以在单个 ALTER TABLE ... ADD PARTITION 语句中添加多个分区，如下所示：You can add multiple partitions in a single ALTER TABLE ... ADD PARTITION statement as shown here:

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

ALTER TABLE employees ADD PARTITION (
    PARTITION p5 VALUES LESS THAN (2010),
    PARTITION p6 VALUES LESS THAN MAXVALUE
);

幸运的是，MySQL 的分区实现提供了在不丢失数据的情况下重新定义分区的方法。我们先来看看几个涉及 RANGE 分区的简单示例。回想一下 members 表，现在定义如下：Fortunately, MySQL's partitioning implementation provides ways to redefine partitions without losing data. Let us look first at a couple of simple examples involving RANGE partitioning. Recall the members table which is now defined as shown here:

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 COLLATE=utf8mb4_0900_ai_ci
/*!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 扩展来实现这一点：Suppose that you would like to move all rows representing members born before 1960 into a separate partition. As we have already seen, this cannot be done using ALTER TABLE ... ADD PARTITION. However, you can use another partition-related extension to ALTER TABLE to accomplish this:

ALTER TABLE members REORGANIZE PARTITION n0 INTO (
    PARTITION s0 VALUES LESS THAN (1960),
    PARTITION s1 VALUES LESS THAN (1970)
);

实际上，该命令将分区 n0 拆分为两个新分区 s0 和 s1。它还会根据两个 n0 子句中包含的规则，将存储在 PARTITION ... VALUES ... 中的数据移动到新分区中，因此 s0 仅包含 YEAR(dob) 小于 1960 的记录，而 s1 包含 YEAR(dob) 大于或等于 1960 但小于 1970 的行。In effect, this command splits partition n0 into two new partitions s0 and s1. It also moves the data that was stored in n0 into the new partitions according to the rules embodied in the two PARTITION ... VALUES ... clauses, so that s0 contains only those records for which YEAR(dob) is less than 1960 and s1 contains those rows in which YEAR(dob) is greater than or equal to 1960 but less than 1970.

REORGANIZE PARTITION 子句也可用于合并相邻分区。您可以按照下面的方式撤消之前语句对 members 表的影响：A REORGANIZE PARTITION clause may also be used for merging adjacent partitions. You can reverse the effect of the previous statement on the members table as shown here:

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (1970)
);

在使用 REORGANIZE PARTITION 拆分或合并分区时不会丢失任何数据。执行上述语句时，MySQL 会将原先存储在分区 s0 和 s1 中的所有记录移动到分区 p0 中。No data is lost in splitting or merging partitions using REORGANIZE PARTITION. In executing the above statement, MySQL moves all of the records that were stored in partitions s0 and s1 into partition p0.

REORGANIZE PARTITION 的一般语法如下所示：The general syntax for REORGANIZE PARTITION is shown here:

ALTER TABLE tbl_name
    REORGANIZE PARTITION partition_list
    INTO (partition_definitions);

这里，tbl_name 是分区表的名称，partition_list 是一个以逗号分隔的现有分区名称列表，用于指定要更改的分区。partition_definitions 是一个以逗号分隔的新分区定义列表，其规则与在 CREATE TABLE 中使用的 partition_definitions 列表相同。在使用 REORGANIZE PARTITION 时，你不限于将多个分区合并为一个，或将一个分区拆分为多个。例如，你可以将 members 表的四个分区重新组织为两个，如下所示：Here, tbl_name is the name of the partitioned table, and partition_list is a comma-separated list of names of one or more existing partitions to be changed. partition_definitions is a comma-separated list of new partition definitions, which follow the same rules as for the partition_definitions list used in CREATE TABLE. You are not limited to merging several partitions into one, or to splitting one partition into many, when using REORGANIZE PARTITION. For example, you can reorganize all four partitions of the members table into two, like this:

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);

你也可以对按 LIST 分区的表使用 REORGANIZE PARTITION。让我们回到向按列表分区的 tt 表添加新分区的问题，并因为新分区的值已经存在于某个已有分区的值列表中而失败。我们可以通过添加一个只包含不冲突值的新分区来处理这个问题，然后重新组织新分区和已有分区，使原本存储在已有分区中的值现在被移动到新分区：You can also use REORGANIZE PARTITION with tables that are partitioned by LIST. Let us return to the problem of adding a new partition to the list-partitioned tt table and failing because the new partition had a value that was already present in the value-list of one of the existing partitions. We can handle this by adding a partition that contains only nonconflicting values, and then reorganizing the new partition and the existing one so that the value which was stored in the existing one is now moved to the new one:

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

在使用 ALTER TABLE ... REORGANIZE PARTITION 对按 RANGE 或 LIST 分区的表重新分区时，需要注意以下几点：Here are some key points to keep in mind when using ALTER TABLE ... REORGANIZE PARTITION to repartition tables that are partitioned by RANGE or LIST: