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

26.3.3 交换分区和子分区与表

在 MySQL 8.4 中，可以使用 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt 语句来交换一个表的分区或子分区与另一个非分区表，前提是以下条件成立：

  1. 表 nt 本身不是分区表。

  2. 表 nt 不是一个临时表。

  3. 表 pt 和 nt 的结构是完全相同的。

  4. 表 nt 不包含任何外键引用，并且没有其他表有指向 nt 的外键引用。

  5. 在 p 分区定义的边界内，没有行位于表 nt 中。这一点不适用于使用了 WITHOUT VALIDATION 的情况。

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

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

  8. 对于 pt 表的任何分区级别的 MAX_ROWS 设置必须与 nt 表的表级别 MAX_ROWS 值相同。同样地，pt 表的任何分区级别的 MIN_ROWS 设置必须与 nt 表的表级别 MIN_ROWS 值相同。

    这两种情况下都成立，无论 pt 是否有显式的表级别 MAX_ROWS 或 MIN_ROWS 选项在作用中。

  9. AVG_ROW_LENGTH 在两张表 pt 和 nt 之间不能有差异。

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

  11. 两张表都不能使用 TABLESPACE 选项。

除了通常用于 ALTER TABLE 语句的 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 交换的非分区表：

Press CTRL+C to copy
 
ALTER TABLE pt
    EXCHANGE PARTITION p
    WITH TABLE nt;

可选地，你可以在末尾添加 WITH VALIDATION 或 WITHOUT VALIDATION。当使用了 WITHOUT VALIDATION 时，ALTER TABLE ... EXCHANGE PARTITION 操作在交换分区与非分区表时不会执行行级别验证，这允许数据库管理员自行确保行位于分区定义的边界内。WITH VALIDATION 是默认值。

只可以在一个单独的ALTER TABLE EXCHANGE PARTITION语句中交换一个和仅仅一个分区或子分区与一个非分区表。要交换多个分区或子分区，使用多个ALTER TABLE EXCHANGE PARTITION语句。EXCHANGE PARTITION不能与其他ALTER TABLE选项结合使用。分区和（如果适用）子分区的类型可以是MySQL 8.4支持的任何类型或类型。

假设已创建并填充了一个分区表e，如下所示的SQL语句：

Press CTRL+C to copy
 
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客户端完成，如下所示：

Press CTRL+C to copy
 
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中的哪些分区包含行，如下所示：

Press CTRL+C to copy
 
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表，PARTITIONS表中的TABLE_ROWS列给出的行数仅供SQL优化使用，并不是总是准确的。

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

Press CTRL+C to copy
 
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.04 sec)

更准确地说，这个语句会导致在分区中找到行与表中找到行的交换。您可以通过查询信息架构的PARTITIONS表来查看这个发生了什么。之前在分区p0中找到的行现在不再存在：

Press CTRL+C to copy
 
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，您可以看到那个“missing”行现在在那里：

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

要交换的表不一定为空。为了演示这个，我们首先向表e中插入一个新行，使得这行存储在分区p0中，通过选择一个id列值小于50，并在查询PARTITIONS表后验证这个：

Press CTRL+C to copy
 
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语句：

Press CTRL+C to copy
 
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.28 sec)

以下查询的输出显示，在发出ALTER TABLE语句之前在分区p0和表e2中找到的行现在已经交换了位置：

Press CTRL+C to copy
 
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中插入一个值超过分区定义为分区p0表e的边界的id列值的新行；然后，再次尝试交换表与分区：

Press CTRL+C to copy
 
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选项允许这个操作成功进行：

Press CTRL+C to copy
 
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进行。

为了避免在交换一个包含大量行的分区与表时进行耗时的验证，可以通过在ALTER TABLE ... EXCHANGE PARTITION语句中添加WITHOUT VALIDATION来跳过行级验证步骤。

以下示例比较了在执行分区与非分区表交换操作时，带有和不带有验证的时间差异。包含两部分，每部分有一百万行的表（表e）中的p0部分被移除，并且p0被交换到一个含有一百万行的非分区表中。带有WITH VALIDATION的操作耗时为0.74秒，而不带验证的操作仅需0.01秒。

Press CTRL+C to copy
 
# 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，填充该表（与表e相同），然后创建一个空的非分区副本es2的示例：

Press CTRL+C to copy
 
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时没有明确指定子分区名称，我们可以通过在PARTITIONS表的SUBPARTITION_NAME列中包含来自INFORMATION_ SCHEMA的生成名称来获取这些子分区的名称，例如：

Press CTRL+C to copy
 
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交换：

Press CTRL+C to copy
 
mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK, 0 rows affected (0.29 sec)

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

Press CTRL+C to copy
 
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)

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

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

表结构在严格的方式进行比较；分区表和非分区表必须具有相同数量、顺序、名称和类型的列和索引。此外，两者都必须使用相同的存储引擎：

Press CTRL+C to copy
 
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中移除了分区。


PREV   HOME   UP   NEXT
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb