18.3.3. Exchanging Partitions and Subpartitions with Tables

In MySQL 5.6, it is possible to exchange a table partition or subpartition with a table using the ALTER TABLE ... EXCHANGE PARTITION statement—that is, to move any existing rows in the partition or subpartition to the nonpartitioned table, and any existing rows in the nonpartitioned table to the table partition or subpartition.

Such operations are subject to the following conditions:

The table to be exchanged must not be partitioned, but must otherwise have the same table structure as the partitioned table.
The table to be exchanged must not be a temporary table.
Any rows existing in the nonpartitioned table prior to the exchange must lie within the range defined for the partition or subpartition.
The table to be exchanged may not have any foreign keys, nor may any other tables have foreign keys which reference this table.
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.

In addition, you should also be aware of the following effects of ALTER TABLE ... EXCHANGE PARTITION:

Executing this statement does not invoke any triggers on either the partitioned table or the exchanged table.
Any AUTO_INCREMENT columns in the exchanged table are reset.

The complete syntax of the the ALTER TABLE ... EXCHANGE PARTITION statement is shown here, where pt is the partitioned table, p is the partition or subpartition to be exchanged, and t is the nonpartitioned table to be exchanged with p:

ALTER TABLE pt     EXCHANGE PARTITION p     WITH TABLE t;

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 5.6.

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 (1.34 sec)mysql> ALTER TABLE e2 REMOVE PARTITIONING;Query OK, 0 rows affected (0.90 sec)Records: 0  Duplicates: 0  Warnings: 0

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

To exchange partition p0 in table e with table e2, you can use the ALTER TABLE statement shown here:

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

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)

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)

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

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)

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)

You should keep in mind that any rows found in the nonpartitioned table prior to issuing the ALTER TABLE ... EXCHANGE PARTITION statement must meet any 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 cannot be stored in partition p0 of table e because its id column value 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

The IGNORE keyword is accepted, but has no effect when used with EXCHANGE PARTITION, as shown here:

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

You can also exchange a subpartition of a subpartitioned table (see Section 18.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: 0mysql> 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

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

The following ALTER TABLE statement exchanges subpartition p3sp0 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

The comparison of table structures used by MySQL is very strict. 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: 0mysql> SHOW CREATE TABLE es3\G*************************** 1. row ***************************       Table: es3Create Table: CREATE TABLE `es3` (  `id` int(11) NOT NULL,  `fname` varchar(30) DEFAULT NULL,  `lname` varchar(30) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 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: 0mysql> 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