在 MySQL 8.4 中，可以使用 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt 将表分区或子分区与表交换，其中 pt 是分区表，p 是要与非分区表 nt 交换的 pt 的分区或子分区，前提是以下条件成立：In MySQL 8.4, it is possible to exchange a table partition or subpartition with a table using ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt, where pt is the partitioned table and p is the partition or subpartition of pt to be exchanged with unpartitioned table nt, provided that the following statements are true:

除了通常执行 ALTER、INSERT 和 CREATE 权限的 ALTER TABLE 语句外，您必须具有 DROP 权限才能执行 ALTER TABLE ... EXCHANGE PARTITION。In addition to the ALTER, INSERT, and CREATE privileges usually required for ALTER TABLE statements, you must have the DROP privilege to perform ALTER TABLE ... EXCHANGE PARTITION.

您还应该注意以下 ALTER TABLE ... EXCHANGE PARTITION 的影响：You should also be aware of the following effects of ALTER TABLE ... EXCHANGE PARTITION:

以下显示了 ALTER TABLE ... EXCHANGE PARTITION 的语法，其中 pt 是分区表，p 是要交换的分区（或子分区），而 nt 是要与 p 交换的非分区表：The syntax for ALTER TABLE ... EXCHANGE PARTITION is shown here, where pt is the partitioned table, p is the partition (or subpartition) to be exchanged, and nt is the nonpartitioned table to be exchanged with p:

ALTER TABLE pt
    EXCHANGE PARTITION p
    WITH TABLE nt;

可选择地，您可以附加 WITH VALIDATION 或 WITHOUT VALIDATION。 当指定 WITHOUT VALIDATION 时，ALTER TABLE ... EXCHANGE PARTITION 操作在与非分区表交换分区时不会执行逐行验证，从而允许数据库管理员自行承担确保行在分区定义边界内的责任。WITH VALIDATION 是默认值。Optionally, you can append WITH VALIDATION or WITHOUT VALIDATION. When WITHOUT VALIDATION is specified, the ALTER TABLE ... EXCHANGE PARTITION operation does not perform any row-by-row validation when exchanging a partition a nonpartitioned table, allowing database administrators to assume responsibility for ensuring that rows are within the boundaries of the partition definition. WITH VALIDATION is the default.

在单个ALTER TABLE EXCHANGE PARTITION语句中，只能将一个分区或子分区与一个非分区表交换。要交换多个分区或子分区，请使用多个ALTER TABLE EXCHANGE PARTITION语句。EXCHANGE PARTITION不能与其他ALTER TABLE选项组合使用。分区表中使用的分区类型以及（如果适用）子分区类型可以是 MySQL 8.4 支持的任何类型。One and only one partition or subpartition may be exchanged with one and only one nonpartitioned table in a single ALTER TABLE EXCHANGE PARTITION statement. To exchange multiple partitions or subpartitions, use multiple ALTER TABLE EXCHANGE PARTITION statements. EXCHANGE PARTITION may not be combined with other ALTER TABLE options. The partitioning and (if applicable) subpartitioning used by the partitioned table may be of any type or types supported in MySQL 8.4.

将分区与非分区表交换Exchanging a Partition with a Nonpartitioned Table

假设已经使用以下 SQL 语句创建并填充了一个分区表 e：Suppose that a partitioned table e has been created and populated using the following SQL statements:

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

INSERT INTO e VALUES
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");

Now we create a nonpartitioned copy of e named e2. This can be done using the mysql client as shown here:

mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

您可以通过查询信息模式 PARTITIONS 表来查看表 e 中哪些分区包含行，如下所示：You can see which partitions in table e contain rows by querying the Information Schema PARTITIONS table, like this:

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
2 rows in set (0.00 sec)

要将表 e 中的分区 p0 与表 e2 交换，可以使用 ALTER TABLE，如下所示：To exchange partition p0 in table e with table e2, you can use ALTER TABLE, as shown here:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.04 sec)

更准确地说，刚刚执行的语句会导致在分区中找到的任何行与在表中找到的行进行交换。通过像以前一样查询信息架构 PARTITIONS 表，你可以观察到这是如何发生的。以前在分区 p0 中找到的表行现在不再存在：More precisely, the statement just issued causes any rows found in the partition to be swapped with those found in the table. You can observe how this has happened by querying the Information Schema PARTITIONS table, as before. The table row that was previously found in partition p0 is no longer present:

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

如果你查询表 e2，你可以看到此 “缺失” 行现在可以在那里找到：If you query table e2, you can see that the “missing” row can now be found there:

mysql> SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

要与分区交换的表不一定必须是空的。为了演示这一点，我们首先在表 e 中插入一行新记录，通过选择一个小于 50 的 id 列值确保该行存储在分区 p0 中，并且之后通过查询 PARTITIONS 表进行验证：The table to be exchanged with the partition does not necessarily have to be empty. To demonstrate this, we first insert a new row into table e, making sure that this row is stored in partition p0 by choosing an id column value that is less than 50, and verifying this afterward by querying the PARTITIONS table:

mysql> INSERT INTO e VALUES (41, "Michael", "Green");
Query OK, 1 row affected (0.05 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';            
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

现在我们再次使用与之前相同的 p0 语句，将分区 e2 与表 ALTER TABLE 交换：Now we once again exchange partition p0 with table e2 using the same ALTER TABLE statement as previously:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.28 sec)

以下查询的输出显示，在发出 p0 语句之前，存储在分区 e2 的表行和存储在表 ALTER TABLE 的表行现在已经交换了位置：The output of the following queries shows that the table row that was stored in partition p0 and the table row that was stored in table e2, prior to issuing the ALTER TABLE statement, have now switched places:

mysql> SELECT * FROM e;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
|   16 | Frank | White |
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS
           FROM INFORMATION_SCHEMA.PARTITIONS
           WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM e2;
+----+---------+-------+
| id | fname   | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

不匹配行Nonmatching Rows

你应该记住，在发出 ALTER TABLE ... EXCHANGE PARTITION 语句之前，在非分区表中找到的任何行都必须满足存储到目标分区的条件，否则该语句将失败。要了解这是如何发生的，首先向 e2 插入一行，该行超出了表 e 的分区 p0 的分区定义边界。例如，插入一行 id 列值过大的数据；然后，再尝试将表与该分区交换：You should keep in mind that any rows found in the nonpartitioned table prior to issuing the ALTER TABLE ... EXCHANGE PARTITION statement must meet the conditions required for them to be stored in the target partition; otherwise, the statement fails. To see how this occurs, first insert a row into e2 that is outside the boundaries of the partition definition for partition p0 of table e. For example, insert a row with an id column value that is too large; then, try to exchange the table with the partition again:

mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.08 sec)

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does not match the partition

只有使用 WITHOUT VALIDATION 选项才允许此操作成功：Only the WITHOUT VALIDATION option would permit this operation to succeed:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.02 sec)

当分区与包含不符合分区定义的行的表进行交换时，修复不匹配行的责任在数据库管理员，这可以使用 REPAIR TABLE 或 ALTER TABLE ... REPAIR PARTITION 来执行。When a partition is exchanged with a table that contains rows that do not match the partition definition, it is the responsibility of the database administrator to fix the non-matching rows, which can be performed using REPAIR TABLE or ALTER TABLE ... REPAIR PARTITION.

不进行逐行验证的分区交换Exchanging Partitions Without Row-By-Row Validation

为了在与具有大量行的表交换分区时避免耗时的验证，可以通过在 WITHOUT VALIDATION 语句后添加 ALTER TABLE ... EXCHANGE PARTITION 来跳过逐行验证步骤。To avoid time consuming validation when exchanging a partition with a table that has many rows, it is possible to skip the row-by-row validation step by appending WITHOUT VALIDATION to the ALTER TABLE ... EXCHANGE PARTITION statement.

下面的示例比较了在与非分区表交换分区时，有验证和无验证的执行时间差异。分区表（表 e）包含两个各有一百万行的分区。表 e 的 p0 中的行被删除，并且 p0 与一个包含一百万行的非分区表进行交换。WITH VALIDATION 操作耗时 0.74 秒。相比之下，WITHOUT VALIDATION 操作耗时 0.01 秒。The following example compares the difference between execution times when exchanging a partition with a nonpartitioned table, with and without validation. The partitioned table (table e) contains two partitions of 1 million rows each. The rows in p0 of table e are removed and p0 is exchanged with a nonpartitioned table of 1 million rows. The WITH VALIDATION operation takes 0.74 seconds. By comparison, the WITHOUT VALIDATION operation takes 0.01 seconds.

# Create a partitioned table with 1 million rows in each partition

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (1000001),
        PARTITION p1 VALUES LESS THAN (2000001),
);

mysql> SELECT COUNT(*) FROM e;
| COUNT(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.27 sec)

# View the rows in each partition

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+-------------+
| PARTITION_NAME | TABLE_ROWS  |
+----------------+-------------+
| p0             |     1000000 |
| p1             |     1000000 |
+----------------+-------------+
2 rows in set (0.00 sec)

# Create a nonpartitioned table of the same structure and populate it with 1 million rows

CREATE TABLE e2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e2;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.24 sec)

# Create another nonpartitioned table of the same structure and populate it with 1 million rows

CREATE TABLE e3 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e3;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.25 sec)

# Drop the rows from p0 of table e

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

# Confirm that there are no rows in partition p0

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Exchange partition p0 of table e with the table e2 'WITH VALIDATION'

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITH VALIDATION;
Query OK, 0 rows affected (0.74 sec)

# Confirm that the partition was exchanged with table e2

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |    1000000 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Once again, drop the rows from p0 of table e

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

# Confirm that there are no rows in partition p0

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION'

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.01 sec)

# Confirm that the partition was exchanged with table e3

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |    1000000 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)
      

如果一个分区与包含不符合分区定义的行的表进行交换，则由数据库管理员负责修复不匹配的行，这可以使用 REPAIR TABLE 或 ALTER TABLE ... REPAIR PARTITION 来完成。If a partition is exchanged with a table that contains rows that do not match the partition definition, it is the responsibility of the database administrator to fix the non-matching rows, which can be performed using REPAIR TABLE or ALTER TABLE ... REPAIR PARTITION.

将子分区与非分区表交换Exchanging a Subpartition with a Nonpartitioned Table

您还可以使用 ALTER TABLE ... EXCHANGE PARTITION 语句，将一个子分区表的子分区（参见 第 26.2.6 节，“子分区”）与非分区表进行交换。在下面的示例中，我们首先创建一个按 RANGE 分区并按 KEY 子分区的表 es，像处理表 e 一样填充该表，然后创建一个空的、非分区的副本表 es2，如下所示：You can also exchange a subpartition of a subpartitioned table (see Section 26.2.6, “Subpartitioning”) with a nonpartitioned table using an ALTER TABLE ... EXCHANGE PARTITION statement. In the following example, we first create a table es that is partitioned by RANGE and subpartitioned by KEY, populate this table as we did table e, and then create an empty, nonpartitioned copy es2 of the table, as shown here:

mysql> CREATE TABLE es (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30)
    -> )
    ->     PARTITION BY RANGE (id)
    ->     SUBPARTITION BY KEY (lname)
    ->     SUBPARTITIONS 2 (
    ->         PARTITION p0 VALUES LESS THAN (50),
    ->         PARTITION p1 VALUES LESS THAN (100),
    ->         PARTITION p2 VALUES LESS THAN (150),
    ->         PARTITION p3 VALUES LESS THAN (MAXVALUE)
    ->     );
Query OK, 0 rows affected (2.76 sec)

mysql> INSERT INTO es VALUES
    ->     (1669, "Jim", "Smith"),
    ->     (337, "Mary", "Jones"),
    ->     (16, "Frank", "White"),
    ->     (2005, "Linda", "Black");
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE es2 LIKE es;
Query OK, 0 rows affected (1.27 sec)

mysql> ALTER TABLE es2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

虽然在创建表 es 时我们没有明确命名任何子分区，但可以通过在从 INFORMATION_SCHEMA 中选择 PARTITIONS 表时包含 SUBPARTITION_NAME 列来获得这些生成的名称，如下所示：Although we did not explicitly name any of the subpartitions when creating table es, we can obtain generated names for these by including the SUBPARTITION_NAME column of the PARTITIONS table from INFORMATION_SCHEMA when selecting from that table, as shown here:

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          3 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)

下面的 ALTER TABLE 语句将表 es 中的子分区 p3sp0 与非分区表 es2 交换：The following ALTER TABLE statement exchanges subpartition p3sp0 in table es with the nonpartitioned table es2:

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK, 0 rows affected (0.29 sec)

您可以通过执行以下查询验证行已被交换：You can verify that the rows were exchanged by issuing the following queries:

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          0 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM es2;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)

如果一个表被子分区，你只能与一个未分区的表交换该表的子分区——而不是整个分区，如下所示：If a table is subpartitioned, you can exchange only a subpartition of the table—not an entire partition—with an unpartitioned table, as shown here:

mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;
ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition

表结构将以严格的方式进行比较；分区表和非分区表的列和索引的数量、顺序、名称及类型必须完全匹配。此外，两个表必须使用相同的存储引擎：Table structures are compared in a strict fashion; the number, order, names, and types of columns and indexes of the partitioned table and the nonpartitioned table must match exactly. In addition, both tables must use the same storage engine:

mysql> CREATE TABLE es3 LIKE e;
Query OK, 0 rows affected (1.31 sec)

mysql> ALTER TABLE es3 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE es3\G
*************************** 1. row ***************************
       Table: es3
Create Table: CREATE TABLE `es3` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> ALTER TABLE es3 ENGINE = MyISAM;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL

本示例中的 ALTER TABLE ... ENGINE ... 语句之所以可行，是因为前一个 ALTER TABLE 已经移除了表 es3 的分区。The ALTER TABLE ... ENGINE ... statement in this example works because the previous ALTER TABLE removed the partitioning from table es3.