Exchanging Partitions and Subpartitions with Tables--官方文档

Suppose that a partitioned table  has been created and populated using the following SQL statements:

INSERT INTO e VALUES
(1669,"Jim","Smith"),(337,"Mary","Jones"),(16,"Frank","White"),(2005,"Linda","Black");

Now we create a nonpartitioned copy of  named . This can be done using the mysql clIEnt as shown here:

 CREATE table e2 liKE e;query OK,0 rows affected (1.34 sec)
MysqL> <strong >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  contain rows by querying the  table,like this:

 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)

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

To exchange partition  in table  with table ,you can use the  statement shown here:

 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  table,as before. The table row that was prevIoUsly found in partition  is no longer present:

 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 ,you can see that the 

 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 ,making sure that this row is stored in partition  by choosing an  column value that is less than 50,and verifying this afterwards by querying the  table:

 INSERT INTO e VALUES (41,"Michael","Green");              query OK,1 row affected (0.05 sec)                                
MysqL> <strong >SELECT PARTITION_name,table_ROWS
->     <strong >FROM informatION_SCHEMA.PARTITIONS
->     <strong >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  with table  using the same  statement as prevIoUsly:

 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  and the table row that was stored in table ,prior to issuing the  statement,have Now switched places:

 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> <strong >SELECT PARTITION_name,table_ROWS
->     <strong >FROM informatION_SCHEMA.PARTITIONS
->     <strong >WHERE table_name = 'e';
+----------------+------------+
| PARTITION_name | table_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)
MysqL> <strong >SELECT * FROM e2;
+----+---------+-------+
| ID | fname   | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

 INSERT INTO e2 VALUES (51,"Ellen","McDonald");query OK,1 row affected (0.08 sec)
MysqL> <strong >ALTER table e EXCHANGE PARTITION p0 WITH table e2;
<span >ERROR 1707 (HY000): Found row that does not match the partition

 ALTER IGnorE table e EXCHANGE PARTITION p0 WITH table e2;

 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> <strong >INSERT INTO es VALUES
->     <strong >(1669,
->     <strong >(337,
->     <strong >(16,
->     <strong >(2005,"Black");
query OK,4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0
MysqL> <strong >CREATE table es2 liKE es;
query OK,0 rows affected (1.27 sec)
MysqL> <strong >ALTER table es2 REMOVE PARTITIONING;
query OK,0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

 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)

 ALTER table es EXCHANGE PARTITION p3sp0 WITH table es2;query OK,0 rows affected (0.29 sec)

 SELECT PARTITION_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> <strong >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: