content_image/687519.jpg content_image/687520.jpg
+------+-------------+------------+ 2 rows in set (0.00 sec) 

You can also get this information using partition selection, as shown here: 

mysql> SELECT * FROM tr PARTITION (p2); +------+-------------+------------+ | id | name | purchased | +------+-------------+------------+ | 2 | alarm clock | 1997-11-05 | | 10 | lava lamp | 1998-12-25 | +------+-------------+------------+ 2 rows in set (0.00 sec) 

See Section 24.5, “Partition Selection” , for more information. 

To drop the partition named p2 , execute the following command: 

mysql> ALTER TABLE tr DROP PARTITION p2; Query OK, 0 rows affected (0.03 sec) 

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

## Note 

The NDBCLUSTER  storage engine does not support ALTER TABLE ... DROP PARTITION . It does, however, support the other partitioning-related extensions to ALTER TABLE  that are described in this chapter. 

It is very important to remember that, when you drop a partition, you also delete all the data that was stored in that partition . You can see that this is the case by re-running the previous SELECT  query: 

mysql> SELECT * FROM tr WHERE purchased -> BETWEEN ’1995-01-01’ AND ’1999-12-31’; Empty set (0.00 sec) 

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

## Note 

DROP PARTITION  is supported by native partitioning in-place APIs and may be used with ALGORITHM={COPY|INPLACE} . DROP PARTITION  with ALGORITHM=INPLACE  deletes data stored in the partition and drops the partition. However, DROP PARTITION  with ALGORITHM=COPY  or old_alter_table=ON rebuilds the partitioned table and attempts to move data from the dropped partition to another partition with a compatible PARTITION ... VALUES  definition. Data that cannot be moved to another partition is deleted. 

Because of this, you must have the DROP  privilege for a table before you can execute ALTER TABLE ... DROP PARTITION  on that table. 

If you wish to drop all data from all partitions while preserving the table definition and its partitioning scheme, use the TRUNCATE TABLE  statement. (See Section 13.1.37, “TRUNCATE TABLE Statement” .) 

If you intend to change the partitioning of a table without  losing data, use ALTER TABLE ... REORGANIZE PARTITION  instead. See below or in Section 13.1.9, “ALTER TABLE Statement” , for information about REORGANIZE PARTITION . 

If you now execute a SHOW CREATE TABLE  statement, you can see how the partitioning makeup of the table has been changed: 

mysql> SHOW CREATE TABLE tr\G *************************** 1. row *************************** Table: tr Create Table: CREATE TABLE ‘tr‘ ( ‘id‘ int(11) DEFAULT NULL, 

