Documentation Home
MySQL 8.3 Reference Manual
Related Documentation
MySQL 8.3 Release Notes
Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL NDB Cluster 8.3
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
MySQL and Linux/Unix
MySQL and macOS
MySQL Partitioning
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL Restrictions and Limitations
Security in MySQL
MySQL and Solaris
Building MySQL from Source
Starting and Stopping MySQL
MySQL Tutorial
MySQL and Windows


MySQL 8.3 Reference Manual  /  ...  /  Exchanging Partitions and Subpartitions with Tables

26.3.3 使用表交换分区和子分区

在 MySQL 8.3 中，可以使用 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt 将分区表或子分区与表交换，其中 pt 是分区表，p 是要与非分区表 nt 交换的分区或子分区，前提是以下语句为真：

  1. 表 nt 不是自己分区的。

  2. 表 nt 不是临时表。

  3. 表 pt 和 nt 的结构否则相同。

  4. 表 nt 不包含外键引用，也没有其他表对 nt 的外键引用。

  5. 表 nt 中没有行位于分区定义 p 的边界之外。如果使用 WITHOUT VALIDATION，则不适用该条件。

  6. 两个表必须使用相同的字符集和排序规则。

  7. 对于 InnoDB 表，两个表必须使用相同的行格式。要确定 InnoDB 表的行格式，请查询 INFORMATION_SCHEMA.INNODB_TABLES。

  8. 任何分区级 MAX_ROWS 设置对于 p 必须与表级 MAX_ROWS 值相同，设置于 nt。任何分区级 MIN_ROWS 设置对于 p 也必须与表级 MIN_ROWS 值相同，设置于 nt。

    无论 pt 是否具有明确的表级 MAX_ROWS 或 MIN_ROWS 选项，都适用该条件。

  9. 表 pt 和 nt 之间的 AVG_ROW_LENGTH 不能不同。

  10. INDEX DIRECTORY 不能在表和要交换的分区之间不同。

  11. 不能在任何表或分区中使用 TABLESPACE 选项。

除了通常需要的 ALTER、INSERT 和 CREATE 权限外，还需要 DROP 权限来执行 ALTER TABLE ... EXCHANGE PARTITION。

您还应该注意以下 ALTER TABLE ... EXCHANGE PARTITION 的效果：

  • 执行 ALTER TABLE ... EXCHANGE PARTITION 不会在分区表或要交换的表上触发任何触发器。

  • 交换表中的任何 AUTO_INCREMENT 列将被重置。

  • IGNORE 关键字对 ALTER TABLE ... EXCHANGE PARTITION 无效。

以下是 ALTER TABLE ... EXCHANGE PARTITION 的语法，其中 pt 是分区表，p 是要交换的分区（或子分区），nt 是要与 p 交换的非分区表：

ALTER TABLE pt
    EXCHANGE PARTITION p
    WITH TABLE nt;

可选地，您可以追加 WITH VALIDATION 或 WITHOUT VALIDATION。当指定 WITHOUT VALIDATION 时，ALTER TABLE ... EXCHANGE PARTITION 操作不会在交换分区和非分区表时执行行-by-row 验证，允许数据库管理员承担确保行在分区定义边界内的责任。WITH VALIDATION 是默认值。

在单个 ALTER TABLE EXCHANGE PARTITION 语句中，只能交换一个分区或子分区与一个非分区表。要交换多个分区或子分区，请使用多个 ALTER TABLE EXCHANGE PARTITION 语句。EXCHANGE PARTITION 不能与其他 ALTER TABLE 选项组合。分区表可以使用 MySQL 8.3 中支持的任何类型或类型的分区和子分区。

交换分区与非分区表

假设已经创建并填充了分区表 e，使用以下 SQL 语句：

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

现在，我们创建了 e 的非分区副本 e2。这可以使用 mysql 客户端，如下所示：

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

您可以通过查询 Information Schema PARTITIONS 表来查看表 e 中哪些分区包含行，如下所示：

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

对于分区的 InnoDB 表，Information Schema PARTITIONS 表中的行计数列 TABLE_ROWS 只是一个估算值，用于 SQL 优化，不总是准确的。

要交换表 e 中的分区 p0 与表 e2，可以使用 ALTER TABLE，如下所示：

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

更确切地说，刚刚发出的语句使得分区中的行与表中的行交换了。您可以通过查询 Information Schema PARTITIONS 表来观察这种情况。之前在分区 p0 中找到的表行现在不再存在：

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，您可以看到之前在分区 p0 中找到的行现在可以在那里找到：

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

要交换的表不一定是空的。为了证明这一点，我们首先将新行插入表 e，确保该行存储在分区 p0 中，然后通过查询 PARTITIONS 表来验证：

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)

现在，我们再次使用相同的 ALTER TABLE 语句交换分区 p0 与表 e2：

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

以下查询的输出显示了之前存储在分区 p0 中的表行和之前存储在表 e2 中的表行现在已经交换了位置：

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)

不匹配的行

请牢记，在发出 ALTER TABLE ... EXCHANGE PARTITION 语句之前，在非分区表中找到的任何行都必须满足目标分区的条件；否则，语句将失败。要看到这是如何发生的，首先在 e2 中插入一行，该行超出表 e 的分区定义 p0 的边界。例如，插入一个 id 列值太大的行；然后，尝试再次交换表与分区：

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 选项才允许此操作成功：

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。

无行-by-行验证的分区交换

为了避免在交换具有许多行的表时进行耗时的验证，可以通过将 WITHOUT VALIDATION 附加到 ALTER TABLE ... EXCHANGE PARTITION 语句中来跳过行-by-行验证步骤。

以下示例比较了在交换具有 1 百万行的非分区表时，带和不带验证的执行时间的差异。分区表（表 e）包含两个各有 1 百万行的分区。从表 e 的 p0 中删除行，并将 p0 与具有 1 百万行的非分区表交换。WITH VALIDATION 操作需要 0.74 秒。相比之下，WITHOUT VALIDATION 操作需要 0.01 秒。

# 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。

与非分区表交换子分区

您也可以使用 ALTER TABLE ... EXCHANGE PARTITION 语句将子分区表（参见 第 26.2.6 节，“子分区”）与非分区表交换。在以下示例中，我们首先创建一个表 es，该表按 RANGE 分区并按 KEY 子分区，然后填充该表，如同我们对表 e 所做的那样，然后创建一个空的、非分区的表副本 es2，如下所示：

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 列来获取生成的子分区名称，如下所示：

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 交换：

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

您可以通过发出以下查询来验证行是否被交换：

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)

如果表是子分区的，您只能将该表的一个子分区与非分区表交换，而不是整个分区，如下所示：

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

表结构将以严格的方式进行比较；分区表和非分区表的列数、顺序、名称和类型必须完全匹配。此外，两个表必须使用相同的存储引擎：

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

PREV   HOME   UP   NEXT
Related Documentation
MySQL 8.3 Release Notes
Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL NDB Cluster 8.3
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
MySQL and Linux/Unix
MySQL and macOS
MySQL Partitioning
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL Restrictions and Limitations
Security in MySQL
MySQL and Solaris
Building MySQL from Source
Starting and Stopping MySQL
MySQL Tutorial
MySQL and Windows