How to Create Partitioned Tables in MySQL: Syntax and Example

To create a partitioned table in MySQL, use the PARTITION BY clause in the CREATE TABLE statement, specifying the partition type and columns. This divides the table data into smaller, manageable parts called partitions for better performance and maintenance.

Syntax

The basic syntax to create a partitioned table in MySQL includes the PARTITION BY clause after the table definition. You specify the partition type such as RANGE, LIST, HASH, or KEY, followed by the partitioning expression and the individual partitions.

• PARTITION BY RANGE(column): Divides data based on ranges of values.
• PARTITION BY LIST(column): Divides data based on a list of values.
• PARTITION BY HASH(column): Divides data using a hash function.
• PARTITION BY KEY(column): Similar to HASH but uses MySQL internal function.

Each partition is defined with a name and a condition (e.g., VALUES LESS THAN for RANGE).

sql

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
)
PARTITION BY partition_type (column) (
  PARTITION p0 VALUES LESS THAN (value1),
  PARTITION p1 VALUES LESS THAN (value2),
  ...
);

Example

This example creates a partitioned table named orders partitioned by RANGE on the order_year column. Each partition holds orders for a specific year range.

sql

CREATE TABLE orders (
  order_id INT NOT NULL,
  order_year INT NOT NULL,
  customer_name VARCHAR(50),
  amount DECIMAL(10,2),
  PRIMARY KEY (order_id, order_year)
)
PARTITION BY RANGE (order_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
);

-- Insert sample data
INSERT INTO orders VALUES (1, 2019, 'Alice', 100.00);
INSERT INTO orders VALUES (2, 2020, 'Bob', 150.00);
INSERT INTO orders VALUES (3, 2021, 'Carol', 200.00);

-- Query to check data
SELECT * FROM orders ORDER BY order_year;

Output

order_id | order_year | customer_name | amount
---------|------------|---------------|--------
1        | 2019       | Alice         | 100.00
2        | 2020       | Bob           | 150.00
3        | 2021       | Carol         | 200.00

Common Pitfalls

Common mistakes when creating partitioned tables in MySQL include:

• Not including the partitioning column in the PRIMARY KEY or UNIQUE keys, which is required.
• Using unsupported data types for partitioning columns, such as BLOB or TEXT.
• Defining overlapping ranges or missing the MAXVALUE partition in RANGE partitions, causing errors.
• Trying to partition on multiple columns without using KEY partitioning.

Example of a wrong and right way:

sql

-- Wrong: Missing partition column in primary key
CREATE TABLE wrong_table (
  id INT NOT NULL,
  order_year INT NOT NULL,
  PRIMARY KEY (id)
)
PARTITION BY RANGE (order_year) (
  PARTITION p0 VALUES LESS THAN (2020),
  PARTITION p1 VALUES LESS THAN (2025)
);

-- Right: Include partition column in primary key
CREATE TABLE right_table (
  id INT NOT NULL,
  order_year INT NOT NULL,
  PRIMARY KEY (id, order_year)
)
PARTITION BY RANGE (order_year) (
  PARTITION p0 VALUES LESS THAN (2020),
  PARTITION p1 VALUES LESS THAN (2025)
);

Quick Reference

Partition Type	Description	Example Usage
RANGE	Partitions data by ranges of column values	PARTITION BY RANGE (year)
LIST	Partitions data by specific list of values	PARTITION BY LIST (region)
HASH	Partitions data using a hash function on column	PARTITION BY HASH (id) PARTITIONS 4
KEY	Partitions data using MySQL internal key hashing	PARTITION BY KEY (id) PARTITIONS 4

Key Takeaways

Use the PARTITION BY clause in CREATE TABLE to define partitions in MySQL.

Include the partitioning column in all PRIMARY KEY or UNIQUE keys to avoid errors.

Choose the right partition type (RANGE, LIST, HASH, KEY) based on your data and queries.

Always define a MAXVALUE partition for RANGE partitions to catch all other values.

Avoid unsupported data types for partition columns like BLOB or TEXT.