MySQL 8 Administrator???s Guide

RANGE and LIST partition management

Partition adding and dropping is handled in a similar way for the RANGE and LIST partition types. A table partitioned by RANGE or LIST partitioning can be dropped using the ALTER TABLE statement with the DROP PARTITION option available.

Make sure you have the DROP privilege before executing the ALTER TABLE ... DROP PARTITION statement. DROP PARTITION will delete all the data and also remove the partition from the table partition definition.

The following example illustrates the DROP PARTITION option with the ALTER TABLE statement:

SET @@SQL_MODE = '';CREATE TABLE employee ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(25) NOT NULL, last_name VARCHAR(25) NOT NULL, store_id INT NOT NULL, ...