用表交换分区和子分区

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

1. 表nt本身未分区。
2. 表nt不是临时表。
3. 表的结构pt与nt其他结构相同。
4. 该表不nt包含任何外键引用，并且其他任何表都没有任何引用的外键nt。
5. 中没有行nt位于的分区定义的边界之外p。如果WITHOUT VALIDATION使用，则此条件不适用。
6. 对于InnoDB表，两个表使用相同的行格式。要确定InnoDB表的行格式，请查询INFORMATION_SCHEMA.INNODB_TABLES。
7. nt没有使用该DATA DIRECTORY选项的任何分区。对于InnoDBMySQL 8.0.14及更高版本中的表，取消了此限制。

除了ALTER，INSERT和CREATE通常需要的权限ALTER TABLE声明，你必须有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交换分区分区表时操作不执行任何一行一行地验证，允许数据库管理员承担确保行是分区定义的范围内承担责任。WITH VALIDATION是默认值。

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

用未分区表交换分区

假设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

您可以e通过查询INFORMATION_SCHEMA.PARTITIONS表来参见表中哪些分区包含行，如下所示：

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)

注意

对于分区InnoDB表，在给定的行数TABLE_ROWS的列INFORMATION_SCHEMA.PARTITIONS表仅仅是一个估计值在SQL优化使用，并不总是准确的。

要将p0table中的分区e与table 交换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)

如果查询table e2，您会发现现在可以在此处找到“ missing ”行：

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

与分区交换的表不一定必须为空。为了证明这一点，我们首先在表中插入新行e，p0通过选择id小于50 的列值并随后通过查询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)

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

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

以下查询的输出显示，在发出该语句之前，存储在partition中p0的表行和存储在table中的表行现在已切换位置：e2ALTER TABLE

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语句之前在未分区表中找到的任何行都必须满足将它们存储在目标分区中所需的条件；否则，该语句将失败。要参见这种情况是如何发生的，请首先在table e2的partition的分区定义的边界之外的行中插入一行。例如，插入一行的列值太大；然后，尝试再次与分区交换表：p0eid

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。

没有行逐行验证的分区交换

为避免在将分区与具有很多行的表交换分区时花费大量时间进行验证，可以通过WITHOUT VALIDATION在ALTER TABLE ... EXCHANGE PARTITION语句后附加跳过逐行验证步骤。

以下示例比较了通过和不通过验证交换具有非分区表的分区时执行时间之间的差异。分区表（table e）包含两个分区，每个分区有100万行。表e中p0中的行被删除，并且p0与一百万行的未分区表交换。该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。在下面的示例中，我们首先创建一个表es，该表按RANGE和进行分区，然后KEY像表table一样填充该表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，但可以通过在从该表中进行选择时包括表的SUBPARTITION_NAME列来获取这些子分区的生成名称，如下所示：PARTITIONSINFORMATION_SCHEMA

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语句将p3sp0表es中的子分区与未分区的表交换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
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