content_image/680647.jpg content_image/680646.jpg content_image/680645.jpg content_image/680644.jpg
mysql> DELETE FROM e WHERE id < 1000001; Query OK, 1000000 rows affected (5.55 sec) 

# Confirm that there are no rows in partition p0 

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ’e’; 

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

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 e2 

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ’e’; 

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

2 rows in set (0.00 sec) 

# Once again, drop the rows from p0 of table e 

mysql> DELETE FROM e WHERE id < 1000001; Query OK, 1000000 rows affected (5.55 sec) 

# Confirm that there are no rows in partition p0 

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ’e’; 

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

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 e3 

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ’e’; 

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

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 using REPAIR TABLE  or ALTER TABLE ... REPAIR PARTITION . 

