content_image/865009.jpg
ALTER TABLE ... ENGINE = ...  changes only the storage engine used by the table, and leaves the table’s partitioning scheme intact. The statement succeeds only if the target storage engine provides partitioning support. You can use ALTER TABLE ... REMOVE PARTITIONING  to remove a table’s partitioning; see Section 13.1.9, “ALTER TABLE Statement” . 

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

## Important 

Only a single PARTITION BY , ADD PARTITION , DROP PARTITION , REORGANIZE PARTITION , or COALESCE PARTITION  clause can be used in a given ALTER TABLE  statement. If you (for example) wish to drop a partition and reorganize a table’s remaining partitions, you must do so in two separate ALTER TABLE statements (one using DROP PARTITION  and then a second one using REORGANIZE PARTITION ). 

You can delete all rows from one or more selected partitions using ALTER TABLE ... TRUNCATE PARTITION . 

## 24.3.1 Management of RANGE and LIST Partitions 

Adding and dropping of range and list partitions are handled in a similar fashion, so we discuss the management of both sorts of partitioning in this section. For information about working with tables that are partitioned by hash or key, see Section 24.3.2, “Management of HASH and KEY Partitions” . 

Dropping a partition from a table that is partitioned by either RANGE  or by LIST  can be accomplished using the ALTER TABLE  statement with the DROP PARTITION  option. Suppose that you have created a table that is partitioned by range and then populated with 10 records using the following CREATE TABLE  and INSERT  statements: 

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE) -> PARTITION BY RANGE( YEAR(purchased) ) ( -> PARTITION p0 VALUES LESS THAN (1990), -> PARTITION p1 VALUES LESS THAN (1995), -> PARTITION p2 VALUES LESS THAN (2000), -> PARTITION p3 VALUES LESS THAN (2005), -> PARTITION p4 VALUES LESS THAN (2010), -> PARTITION p5 VALUES LESS THAN (2015) -> ); Query OK, 0 rows affected (0.28 sec) mysql> INSERT INTO tr VALUES -> (1, ’desk organiser’, ’2003-10-15’), -> (2, ’alarm clock’, ’1997-11-05’), -> (3, ’chair’, ’2009-03-10’), -> (4, ’bookcase’, ’1989-01-10’), -> (5, ’exercise bike’, ’2014-05-09’), -> (6, ’sofa’, ’1987-06-05’), -> (7, ’espresso maker’, ’2011-11-22’), -> (8, ’aquarium’, ’1992-08-04’), -> (9, ’study desk’, ’2006-09-16’), -> (10, ’lava lamp’, ’1998-12-25’); Query OK, 10 rows affected (0.05 sec) Records: 10 Duplicates: 0 Warnings: 0 

You can see which items should have been inserted into partition p2  as shown here: 

mysql> SELECT * FROM tr -> WHERE purchased BETWEEN ’1995-01-01’ AND ’1999-12-31’; +------+-------------+------------+ | id | name | purchased | +------+-------------+------------+ | 2 | alarm clock | 1997-11-05 | | 10 | lava lamp | 1998-12-25 | 

