Use the YYYYMM function to shard data by year and month - PolarDB

YYYYMM partitions data by year and month. It computes a hash from a shard key's timestamp using the formula (year × 12 + month) % D, where D is the number of database shards, then routes each row to the shard matching that remainder.

For example, YYYYMM('2012-12-31 12:12:12') evaluates as (2012 × 12 + 12) % D, placing December 2012 in the twelfth month slot of 2012.

When to use YYYYMM

YYYYMM is a good fit when:

Data accumulates month by month and you want each month's records in a dedicated table shard.
Queries frequently filter by year and month, and you need those queries to hit a single shard without scanning others.
You want to apply the same time column for both database-level and table-level partitioning in one DDL statement.

We recommend using YYYYMM together with the tbpartition by YYYYMM(ShardKey) clause.

How it works

YYYYMM is a cyclic hash function, not a range function. This distinction affects how you plan your shards:

Each month within a cycle maps to a fixed table shard.
When the cycle repeats, data from the same calendar month in different years lands in the same table shard. For example, with a two-year cycle, March 2014 maps to the same shard as March 2012.
Because the function is cyclic rather than range-based, it cannot automatically generate shards. You must calculate the total number of table shards yourself based on your intended cycle length, then set tbpartitions accordingly.

Requirements

Constraint	Detail
Shard key type	Must be `DATE`, `DATETIME`, or `TIMESTAMP`
Shard count	Calculate total physical table shards before creating the table, based on your cycle length
Data co-location	After a cycle ends, data from the same calendar month in different cycles shares the same table shard

Example

The following example creates a table on a PolarDB-X instance with eight database shards, partitioned by month over a two-year cycle.

Shard calculation:

Cycle length: 2 years = 24 months
Total physical table shards: 24
Table shards per database shard: 24 ÷ 8 = 3

create table test_yyyymm_tb (
    id          int,
    name        varchar(30) DEFAULT NULL,
    create_time datetime    DEFAULT NULL,
    primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
dbpartition by YYYYMM(create_time)
tbpartition by YYYYMM(create_time) tbpartitions 3;

With this setup:

Rows are distributed across the eight database shards based on (year × 12 + month) % 8.
Within each database shard, rows are further split into three table shards.
A query filtering create_time by a specific year and month routes directly to the matching table shard, without scanning other shards.