PolarDB:MMDD

Limits

• The partitioning key must be of the DATE, DATETIME, or TIMESTAMP type.
• This function can be used only for partitioning data into table shards, not into database shards.
• When you use the MMDD function to partition data into table shards, ensure that each database shard has no more than 366 table shards. This is because a year cannot have more than 366 days.
• The version of the PolarDB-X 1.0 instance must be 5.1.28-1320920 or later..

Routing method

To obtain the table shard subscript, divide by the day in the time value of the partitioning key of a database shard.

Scenarios

The MMDD function is applicable to scenarios where data needs to be partitioned into table shards by day of a year. The name subscript of a table shard indicates a specific day of a year.

Examples

Assume that you want to partition data into database shards by user ID and then create a physical table shard for each day and month based on the create_time column. You can use the following Data Definition Language (DDL) statement to create tables:

create table test_mmdd_tb (    
    id int, 
    name varchar(30) DEFAULT NULL,  
    create_time datetime DEFAULT NULL,
    primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
dbpartition by HASH(name) 
tbpartition by MMDD(create_time) tbpartitions 366;