MySQL Partition Generator

Describe the table you want to partition and this generator writes the complete MySQL script: the CREATE TABLE with PARTITION BY RANGE COLUMNS, RANGE, LIST, HASH or KEY, every partition definition, a MAXVALUE catch-all, and the ALTER TABLE statements for rolling partitions forward and dropping old ones. It also catches the errors MySQL will throw, such as a primary key that does not include the partition column (1503) or foreign keys on a partitioned table. Everything runs in your browser.

TableStrategyPartition columnColumn typePrimary key

IntervalStart datePartitionsMAXVALUE partition

Column definitions (one per line, without keys or the partition clause)

Partitions: 13

⚠ Every unique key on a partitioned table, including the primary key, must include all columns used in the partitioning expression, so "created_at" was appended. Without it MySQL fails with ERROR 1503: A PRIMARY KEY must include all columns in the table's partitioning function.

Partitioned CREATE TABLE

CREATE TABLE events (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  country     CHAR(2) NOT NULL,
  payload     JSON NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p2026_01 VALUES LESS THAN ('2026-02-01'),
  PARTITION p2026_02 VALUES LESS THAN ('2026-03-01'),
  PARTITION p2026_03 VALUES LESS THAN ('2026-04-01'),
  PARTITION p2026_04 VALUES LESS THAN ('2026-05-01'),
  PARTITION p2026_05 VALUES LESS THAN ('2026-06-01'),
  PARTITION p2026_06 VALUES LESS THAN ('2026-07-01'),
  PARTITION p2026_07 VALUES LESS THAN ('2026-08-01'),
  PARTITION p2026_08 VALUES LESS THAN ('2026-09-01'),
  PARTITION p2026_09 VALUES LESS THAN ('2026-10-01'),
  PARTITION p2026_10 VALUES LESS THAN ('2026-11-01'),
  PARTITION p2026_11 VALUES LESS THAN ('2026-12-01'),
  PARTITION p2026_12 VALUES LESS THAN ('2027-01-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

Maintenance SQL

-- Split the MAXVALUE catch-all to add next period's partition.
-- Fast while pmax is empty; if it holds rows they are copied.
ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p2027_01 VALUES LESS THAN ('2027-02-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

-- Optional: archive the oldest period first by swapping it into an
-- empty, identical, non-partitioned table (a metadata-only operation)
CREATE TABLE events_archive LIKE events;
ALTER TABLE events_archive REMOVE PARTITIONING;
ALTER TABLE events EXCHANGE PARTITION p2026_01 WITH TABLE events_archive;

-- Remove the oldest period instantly (deletes any rows still in it)
ALTER TABLE events DROP PARTITION p2026_01;

-- Alternative: keep the partition definition but empty it
-- ALTER TABLE events TRUNCATE PARTITION p2026_01;

-- Row counts and size per partition
SELECT PARTITION_NAME, PARTITION_DESCRIPTION, TABLE_ROWS,
       ROUND(DATA_LENGTH / 1024 / 1024, 1) AS data_mb
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'events'
ORDER BY PARTITION_ORDINAL_POSITION;

-- Check pruning: the "partitions" column of EXPLAIN should list one partition
EXPLAIN SELECT COUNT(*) FROM events
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

Do more than mysql partition generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the table name, column definitions and primary key, then pick the partition column and its data type.
Choose RANGE for dates or growing IDs, LIST for fixed groups such as regions, or HASH/KEY to spread rows evenly, and set the interval, groups or bucket count.
Copy the CREATE TABLE script, read the warnings, and keep the maintenance SQL for adding next month's partition and dropping expired ones.

Frequently asked questions

What is the difference between RANGE and RANGE COLUMNS in MySQL?

PARTITION BY RANGE takes a single integer expression, so a date column has to be wrapped in a function such as TO_DAYS() or YEAR(). RANGE COLUMNS compares the column values directly and accepts DATE, DATETIME, string and integer columns, which makes the boundaries readable ('2026-02-01' instead of 740000) and still supports partition pruning. TIMESTAMP is the exception: it can only be partitioned with RANGE (UNIX_TIMESTAMP(col)).

Why does MySQL say a PRIMARY KEY must include all columns in the partitioning function?

MySQL partitions use local indexes — each partition has its own copy of every index. A unique index can therefore only be enforced if the partition column is part of the key, because that tells MySQL which single partition could hold a duplicate. The fix is a composite key such as PRIMARY KEY (id, created_at). AUTO_INCREMENT still works as long as the auto-increment column is the first column of some index.

How do I add a new partition when the table has a MAXVALUE partition?

ADD PARTITION can only append above the highest existing bound, and MAXVALUE is already the highest, so ADD fails with error 1481. Instead split the catch-all with ALTER TABLE t REORGANIZE PARTITION pmax INTO (PARTITION p2027_01 VALUES LESS THAN ('2027-02-01'), PARTITION pmax VALUES LESS THAN (MAXVALUE)). While pmax is empty this is almost instant, so schedule it to run before each new period starts.