content_image/821726.jpg
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 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 8.0. 

## Exchanging a Partition with a Nonpartitioned Table 

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

<img src='content_image/821726.jpg'>

## Note 

For partitioned InnoDB  tables, the row count given in the TABLE_ROWS  column of the INFORMATION_SCHEMA.PARTITIONS  table is only an estimated value used in SQL optimization, and is not always exact. 

