MysqlHow-ToIntermediate · 3 min read

How to Reorganize Partition in MySQL: Syntax and Examples

In MySQL, you can reorganize partitions using the ALTER TABLE ... REORGANIZE PARTITION statement. This command merges existing partitions into new partitions, allowing you to change the partitioning scheme without dropping the table.

📐

Syntax

The ALTER TABLE ... REORGANIZE PARTITION statement merges one or more existing partitions into new partitions. You specify the partitions to merge and define the new partitions in the same statement.

Parts explained:

ALTER TABLE table_name: Specifies the table to modify.
REORGANIZE PARTITION old_partition[, old_partition2, ...]: Lists the partitions to merge.
INTO (PARTITION new_partition VALUES LESS THAN (...), ...): Defines the new partitions replacing the old ones.

sql

ALTER TABLE table_name
REORGANIZE PARTITION old_partition [, old_partition2, ...]
INTO (
  PARTITION new_partition1 VALUES LESS THAN (value1),
  PARTITION new_partition2 VALUES LESS THAN (value2),
  ...
);

💻

Example

This example shows how to reorganize partitions by merging two existing partitions into three new partitions with different ranges.

sql

CREATE TABLE sales (
  id INT NOT NULL,
  sale_date DATE NOT NULL
)
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p0 VALUES LESS THAN (2018),
  PARTITION p1 VALUES LESS THAN (2020),
  PARTITION p2 VALUES LESS THAN (2022)
);

-- Reorganize partitions p1 and p2 into three new partitions
ALTER TABLE sales
REORGANIZE PARTITION p1, p2 INTO (
  PARTITION p1 VALUES LESS THAN (2021),
  PARTITION p2 VALUES LESS THAN (2023),
  PARTITION p3 VALUES LESS THAN MAXVALUE
);

Output

Query OK, 0 rows affected (0.05 sec) Query OK, 0 rows affected (0.03 sec)

⚠️

Common Pitfalls

Incorrect partition names: Using partition names that do not exist causes errors.
Overlapping ranges: New partitions must have non-overlapping ranges; otherwise, MySQL returns an error.
Missing MAXVALUE partition: If you want to cover all remaining values, include a partition with VALUES LESS THAN MAXVALUE.
Unsupported partition types: Reorganize works only with RANGE or LIST partitions, not HASH or KEY.

sql

/* Wrong: Overlapping ranges in new partitions */
ALTER TABLE sales
REORGANIZE PARTITION p1, p2 INTO (
  PARTITION p1 VALUES LESS THAN (2021),
  PARTITION p2 VALUES LESS THAN (2020)  -- Overlaps with p1
);

Output

ERROR 1503 (HY000): A RANGE or LIST partitioning function returns out-of-range value

📊

Quick Reference

Clause	Description
ALTER TABLE table_name	Specifies the table to modify partitions
REORGANIZE PARTITION old_partitions	Lists partitions to merge or reorganize
INTO (PARTITION new_part VALUES LESS THAN (value), ...)	Defines new partitions replacing old ones
VALUES LESS THAN MAXVALUE	Covers all values greater than previous partitions
Supported Partition Types	RANGE and LIST only

✅

Key Takeaways

Use ALTER TABLE ... REORGANIZE PARTITION to merge and redefine partitions in MySQL.

Ensure new partitions have non-overlapping ranges and cover all data.

Only RANGE and LIST partition types support reorganization.

Always verify partition names exist before reorganizing.

Include a MAXVALUE partition to cover all remaining values if needed.