19.3.3 与表交换分区和子分区

MySQL 8.0 参考手册 / 第24章分区 / 24.3 分区管理 / 19.3.3 与表交换分区和子分区

19.3.3 与表交换分区和子分区

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

表nt本身没有分区。
表nt不是临时表。
pt表的结构nt在其他方面是相同的。
表nt不包含外键引用，并且没有其他表具有引用的任何外键nt。
中没有行nt位于的分区定义边界之外 p。
两个表必须使用相同的字符集和排序规则。
对于InnoDB表，两个表必须使用相同的行格式。要确定 InnoDB表的行格式，请查询 INFORMATION_SCHEMA.INNODB_SYS_TABLES.
的任何分区级MAX_ROWS设置 p都必须与为 MAX_ROWS设置的表级值相同nt。的任何分区级 MIN_ROWS设置的设置p 也必须与为设置的任何表级 MIN_ROWS值相同nt。
这在任何一种情况下都是正确的，无论是否 pt有一个明确的表级 MAX_ROWS或MIN_ROWS 选项有效。
两个表和之间的AVG_ROW_LENGTH不能不同。 ptnt
pt没有任何使用该DATA DIRECTORY选项的分区。
INDEX DIRECTORY表和要与之交换的分区之间不能不同。
TABLESPACE任何一个表中都不能使用表或分区选项。

除了语句通常需要的、和权限ALTER外 INSERT，您还必须具有执行 . CREATEALTER TABLEDROPALTER 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;

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

与非分区表交换分区

假设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 现在我们创建一个named 的非分区副本e2。这可以使用 mysql客户端来完成，如下所示：

Press CTRL+C to copy
 
mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (1.34 sec)

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

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

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 |
+----------------+------------+
4 rows in set (0.00 sec)

笔记

对于分区表，表列中 InnoDB给出的行数只是 SQL 优化中使用的估计值，并不总是准确的。 TABLE_ROWSINFORMATION_SCHEMA.PARTITIONS

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

更准确地说，刚刚发出的语句导致分区中找到的任何行与表中找到的行交换。INFORMATION_SCHEMA.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，你可以看到现在可以在那里找到 “丢失”的行：

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

要与分区交换的表不一定非得是空的。为了演示这一点，我们首先向表中插入一个新行，通过选择一个小于 50 的列值 e确保该行存储在分区中，然后通过查询表来验证这一点： p0idPARTITIONS

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与表交换：e2ALTER TABLE

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

以下查询的输出显示存储在 partitionp0中的表行和存储在 table 中的表行，e2在发出 ALTER TABLE语句之前，现在已经交换了位置：

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的分区定义边界之外的行。例如，插入列值过大的行；然后，再次尝试将表与分区交换： p0eid

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

关键字被接受，但与IGNORE一起使用时无效EXCHANGE PARTITION，如下所示：

Press CTRL+C to copy
 
mysql> ALTER IGNORE TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does not match the partition

与非分区表交换子分区

您还可以使用语句将子分区表的子分区（请参阅第 19.2.6 节，“子分区”）与非分区表交换ALTER TABLE ... EXCHANGE PARTITION。在以下示例中，我们首先创建一个es由分区RANGE和子分区的 KEY表，像对表一样填充此表 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包含表的来获取这些子分区的生成名称，如下所示： SUBPARTITION_NAMEPARTITIONSINFORMATION_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交换： p3sp0eses2

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

MySQL使用的表结构比较非常严格。分区表和非分区表的列和索引的数量、顺序、名称和类型必须完全匹配。此外，两个表必须使用相同的存储引擎：

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=latin1
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