content_image/855584.jpg
MySQL 5.1 and later MySQL versions, or for creating tables partitioned by KEY  or LINEAR KEY  on a MySQL 5.5 or later server which can be used on a MySQL 5.1 server. 

The table that results from using an ALTER TABLE ... PARTITION BY  statement must follow the same rules as one created using CREATE TABLE ... PARTITION BY . This includes the rules governing the relationship between any unique keys (including any primary key) that the table might have, and the column or columns used in the partitioning expression, as discussed in Section 24.6.1, “Partitioning Keys, Primary Keys, and Unique Keys” . The CREATE TABLE ... PARTITION BY  rules for specifying the number of partitions also apply to ALTER TABLE ... PARTITION BY . 

The partition_definition  clause for ALTER TABLE ADD PARTITION  supports the same options as the clause of the same name for the CREATE TABLE  statement. (See Section 13.1.20, “CREATE TABLE Statement” , for the syntax and description.) Suppose that you have the partitioned table created as shown here: 

CREATE TABLE t1 ( id INT, year_col INT ) PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999) ); 

You can add a new partition p3  to this table for storing values less than 2002  as follows: 

ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002)); 

DROP PARTITION  can be used to drop one or more RANGE  or LIST  partitions. This statement cannot be used with HASH  or KEY  partitions; instead, use COALESCE PARTITION  (see later in this section). Any data that was stored in the dropped partitions named in the partition_names  list is discarded. For example, given the table t1  defined previously, you can drop the partitions named p0  and p1  as shown here: 

ALTER TABLE t1 DROP PARTITION p0, p1; 

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

## Note 

DROP PARTITION  does not work with tables that use the NDB  storage engine. See Section 24.3.1, “Management of RANGE and LIST Partitions” , and Section 23.1.7, “Known Limitations of NDB Cluster” . 

ADD PARTITION  and DROP PARTITION  do not currently support IF [NOT] EXISTS . 

The DISCARD PARTITION ... TABLESPACE  and IMPORT PARTITION ... TABLESPACE  options extend the Transportable Tablespace feature to individual InnoDB  table partitions. Each InnoDB  table partition has its own tablespace file ( .ibd  file). The Transportable Tablespace feature makes it easy to copy the tablespaces from a running MySQL server instance to another running instance, or to perform a 

