Detailed example of MySQL exchange partition

alter table pt exchange partition p with table nt;

# Create a partition table
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 several records into partition table e INSERT INTO e VALUES
  (1669, "Jim", "Smith"),
  (337, "Mary", "Jones"),
  (16, "Frank", "White"),
  (2005, "Linda", "Black");

# Create a table e2
mysql> create table e2 like e;

# Delete the partition of table e2 to make it a normal table mysql> alter table e2 remove partitioning;

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> alter table e exchange partition p0 with table e2;

# The data in partition P0 of table e is gonemysql> 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)

# In table e2, there is a record from partition p0 of table emysql> SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

# Add a record in partition p0 to table e mysql> INSERT INTO e VALUES (41, "Michael", "Green");      
Query OK, 1 row affected (0.05 sec)               

# Confirm that the inserted data is indeed stored in partition p0
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> alter table e exchange partition p0 with table e2;
Query OK, 0 rows affected (0.28 sec)

# Previously, p0 of table e was exchanged to a piece of data in table e2, and now it is back to table e
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 * FROM e2;
+----+---------+-------+
| id | fname | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

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

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

# The data with id 51 is also stored in p0 of table e mysql> select * from e partition(p0);
+------+---------+----------+
| id | fname | lname |
+------+---------+----------+
| 41 | Michael | Green |
| 51 | Ellen | McDonald | 
+------+---------+----------+

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