Movatterモバイル変換

In MySQL 8.0, it is possible to exchange a table partition or subpartition with a table usingALTER TABLEpt EXCHANGE PARTITIONp WITH TABLEnt, wherept is the partitioned table andp is the partition or subpartition ofpt to be exchanged with unpartitioned tablent, provided that the following statements are true:

In addition to theALTER,INSERT, andCREATE privileges usually required forALTER TABLE statements, you must have theDROP privilege to performALTER TABLE ... EXCHANGE PARTITION.

You should also be aware of the following effects ofALTER TABLE ... EXCHANGE PARTITION:

The syntax forALTER TABLE ... EXCHANGE PARTITION is shown here, wherept is the partitioned table,p is the partition (or subpartition) to be exchanged, andnt is the nonpartitioned table to be exchanged withp:

ALTER TABLEpt    EXCHANGE PARTITIONp    WITH TABLEnt;

Optionally, you can appendWITH VALIDATION orWITHOUT VALIDATION. WhenWITHOUT VALIDATION is specified, theALTER TABLE ... EXCHANGE PARTITION operation does not perform any row-by-row validation when exchanging a partition a nonpartitioned table, allowing database administrators to assume responsibility for ensuring that rows are within the boundaries of the partition definition.WITH VALIDATION is the default.

One and only one partition or subpartition may be exchanged with one and only one nonpartitioned table in a singleALTER TABLE EXCHANGE PARTITION statement. To exchange multiple partitions or subpartitions, use multipleALTER TABLE EXCHANGE PARTITION statements.EXCHANGE PARTITION may not be combined with otherALTER TABLE options. The partitioning and (if applicable) subpartitioning used by the partitioned table may be of any type or types supported in MySQL 8.0.

Exchanging a Partition with a Nonpartitioned Table

Suppose that a partitioned tablee 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 ofe namede2. This can be done using themysql client as shown here:

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

You can see which partitions in tablee contain rows by querying the Information SchemaPARTITIONS 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 |+----------------+------------+2 rows in set (0.00 sec)

To exchange partitionp0 in tablee with tablee2, you can useALTER TABLE, as shown here:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;Query OK, 0 rows affected (0.04 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 SchemaPARTITIONS table, as before. The table row that was previously found in partitionp0 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 tablee2, 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 tablee, making sure that this row is stored in partitionp0 by choosing anid column value that is less than 50, and verifying this afterward by querying thePARTITIONS 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 partitionp0 with tablee2 using the sameALTER 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 partitionp0 and the table row that was stored in tablee2, prior to issuing theALTER 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)

Nonmatching Rows

You should keep in mind that any rows found in the nonpartitioned table prior to issuing theALTER TABLE ... EXCHANGE PARTITION statement must meet the conditions required for them to be stored in the target partition; otherwise, the statement fails. To see how this occurs, first insert a row intoe2 that is outside the boundaries of the partition definition for partitionp0 of tablee. For example, insert a row with anid column value that 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

Only theWITHOUT VALIDATION option would permit this operation to succeed:

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

When a partition is exchanged with a table that contains rows that do not match the partition definition, it is the responsibility of the database administrator to fix the non-matching rows, which can be performed usingREPAIR TABLE orALTER TABLE ... REPAIR PARTITION.

Exchanging Partitions Without Row-By-Row Validation

To avoid time consuming validation when exchanging a partition with a table that has many rows, it is possible to skip the row-by-row validation step by appendingWITHOUT VALIDATION to theALTER TABLE ... EXCHANGE PARTITION statement.

The following example compares the difference between execution times when exchanging a partition with a nonpartitioned table, with and without validation. The partitioned table (tablee) contains two partitions of 1 million rows each. The rows in p0 of table e are removed and p0 is exchanged with a nonpartitioned table of 1 million rows. TheWITH VALIDATION operation takes 0.74 seconds. By comparison, theWITHOUT VALIDATION operation takes 0.01 seconds.

# Create a partitioned table with 1 million rows in each partitionCREATE 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 partitionSELECT 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 rowsCREATE 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 rowsCREATE 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 emysql> DELETE FROM e WHERE id < 1000001;Query OK, 1000000 rows affected (5.55 sec)# Confirm that there are no rows in partition p0mysql> 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 e2mysql> 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 emysql> DELETE FROM e WHERE id < 1000001;Query OK, 1000000 rows affected (5.55 sec)# Confirm that there are no rows in partition p0mysql> 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 e3mysql> 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)

If a partition is exchanged with a table that contains rows that do not match the partition definition, it is the responsibility of the database administrator to fix the non-matching rows, which can be performed usingREPAIR TABLE orALTER TABLE ... REPAIR PARTITION.

Exchanging a Subpartition with a Nonpartitioned Table

You can also exchange a subpartition of a subpartitioned table (seeSection 26.2.6, “Subpartitioning”) with a nonpartitioned table using anALTER TABLE ... EXCHANGE PARTITION statement. In the following example, we first create a tablees that is partitioned byRANGE and subpartitioned byKEY, populate this table as we did tablee, and then create an empty, nonpartitioned copyes2 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 tablees, we can obtain generated names for these by including theSUBPARTITION_NAME column of thePARTITIONS table fromINFORMATION_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 followingALTER TABLE statement exchanges subpartitionp3sp0 in tablees with the nonpartitioned tablees2:

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

Table structures are compared in a strict fashion; 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 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