MysqlHow-ToBeginner · 4 min read

How to Drop Partition in MySQL: Syntax and Examples

To drop a partition in MySQL, use the ALTER TABLE table_name DROP PARTITION partition_name; command. This removes the specified partition and its data from the table.

📐

Syntax

The syntax to drop a partition in MySQL is:

ALTER TABLE: Command to modify the table structure.
table_name: The name of the table with partitions.
DROP PARTITION: Keyword to remove a partition.
partition_name: The name of the partition to drop.

Note: Dropping a partition deletes all data in that partition.

sql

ALTER TABLE table_name DROP PARTITION partition_name;

💻

Example

This example shows how to drop a partition named p1 from a partitioned table sales:

sql

CREATE TABLE sales (
  id INT,
  sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p0 VALUES LESS THAN (2010),
  PARTITION p1 VALUES LESS THAN (2015),
  PARTITION p2 VALUES LESS THAN (2020)
);

ALTER TABLE sales DROP PARTITION p1;

Output

Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec)

⚠️

Common Pitfalls

Common mistakes when dropping partitions include:

Trying to drop a partition that does not exist causes an error.
Dropping a partition deletes all data in it permanently.
Some partitioning types do not support dropping partitions.
Forcing drop without checking can cause data loss.

Always back up data before dropping partitions.

sql

/* Wrong: Dropping non-existent partition */
ALTER TABLE sales DROP PARTITION p5;

/* Right: Check partitions before dropping */
SHOW CREATE TABLE sales;
ALTER TABLE sales DROP PARTITION p1;

Output

ERROR 1508 (HY000): PARTITION 'p5' not found in table 'sales' /* SHOW CREATE TABLE output shows existing partitions */ Query OK, 0 rows affected

📊

Quick Reference

Command	Description
ALTER TABLE table_name DROP PARTITION partition_name;	Drops the specified partition and deletes its data.
SHOW CREATE TABLE table_name;	Shows table structure and partitions.
ALTER TABLE table_name ADD PARTITION (...);	Adds new partitions to the table.
ALTER TABLE table_name REORGANIZE PARTITION ...;	Merges or splits partitions.

✅

Key Takeaways

Use ALTER TABLE DROP PARTITION to remove a partition and its data.

Dropping a partition permanently deletes all data in that partition.

Verify partition names with SHOW CREATE TABLE before dropping.

Not all partition types support dropping partitions.

Always back up your data before modifying partitions.