MysqlHow-ToBeginner · 4 min read

How to Add Partition in MySQL: Syntax and Examples

In MySQL, you add partitions to a table by defining the PARTITION BY clause during table creation or by reorganizing partitions with ALTER TABLE. You cannot directly add partitions to an existing non-partitioned table; instead, you must create the table with partitions or use ALTER TABLE ... REORGANIZE PARTITION to modify partitions.

📐

Syntax

Partitioning in MySQL is defined when creating a table using the PARTITION BY clause. You specify the partition type and how data is divided. Common partition types include RANGE, LIST, and HASH. You can also add or reorganize partitions using ALTER TABLE.

Key parts:

PARTITION BY RANGE(column): Divides data into ranges based on column values.
PARTITION p0 VALUES LESS THAN (value): Defines a partition with values less than the specified value.
ALTER TABLE table_name ADD PARTITION (PARTITION pX VALUES LESS THAN (value)): Adds a new partition (only for partitioned tables).

sql

CREATE TABLE table_name (
  column1 INT,
  column2 VARCHAR(50)
)
PARTITION BY RANGE(column1) (
  PARTITION p0 VALUES LESS THAN (100),
  PARTITION p1 VALUES LESS THAN (200),
  PARTITION p2 VALUES LESS THAN MAXVALUE
);

-- To add a partition to an existing partitioned table:
ALTER TABLE table_name ADD PARTITION (
  PARTITION p3 VALUES LESS THAN (300)
);

💻

Example

This example creates a table sales partitioned by range on the sale_year column. It then adds a new partition for sales before 2025.

sql

CREATE TABLE sales (
  id INT,
  sale_year INT,
  amount DECIMAL(10,2)
)
PARTITION BY RANGE(sale_year) (
  PARTITION p2019 VALUES LESS THAN (2020),
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

ALTER TABLE sales ADD PARTITION (
  PARTITION p2025 VALUES LESS THAN (2025)
);

Output

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

⚠️

Common Pitfalls

Common mistakes when adding partitions in MySQL include:

Trying to add partitions to a table that was not created with partitions. You must create the table with partitions first.
Using ALTER TABLE ADD PARTITION with overlapping or unordered partition values, which causes errors.
Not including a MAXVALUE partition to catch all remaining values.
Forgetting that some partition types (like LIST) require specific value lists.

sql

/* Wrong: Adding partition to non-partitioned table */
ALTER TABLE non_partitioned_table ADD PARTITION (
  PARTITION p1 VALUES LESS THAN (100)
);

/* Right: Create table with partitions first */
CREATE TABLE partitioned_table (
  id INT
)
PARTITION BY RANGE(id) (
  PARTITION p0 VALUES LESS THAN (100),
  PARTITION p1 VALUES LESS THAN MAXVALUE
);

Output

ERROR 1503 (HY000): A PARTITION clause is not allowed; this is not a partitioned table Query OK, 0 rows affected (0.01 sec)

📊

Quick Reference

Command	Description
CREATE TABLE ... PARTITION BY ...	Create a partitioned table with specified partitioning scheme
ALTER TABLE ... ADD PARTITION (...)	Add new partitions to an existing partitioned table
ALTER TABLE ... REORGANIZE PARTITION ...	Modify existing partitions by splitting or merging
PARTITION pX VALUES LESS THAN (value)	Define partition range upper bound
PARTITION pX VALUES IN (value1, value2)	Define list partition values

✅

Key Takeaways

You must create a table with partitions to use partitioning; you cannot add partitions to a non-partitioned table.

Use the PARTITION BY clause with RANGE, LIST, or HASH to define how data is split.

Add partitions with ALTER TABLE ADD PARTITION only on already partitioned tables.

Always include a MAXVALUE partition to cover all remaining data.

Partition values must be non-overlapping and ordered to avoid errors.