MMDD

This topic describes how to use the MMDD function.

Description

You can use the MMDD function to divide the day in each time value of the shard key of a database shard by 366 to obtain the subscripts for table shards.

Limits

• A shard key must be of the DATE, DATETIME, or TIMESTAMP type.

• The MMDD function can be used only for table sharding. This function cannot be used for database sharding.

• When you use the MMDD function to partition data into table shards, make sure that each database shard has no more than 366 table shards. This is because a year cannot have more than 366 days.

Scenarios

Data needs to be partitioned into table shards by day of a year. The subscript of each table shard name indicates a specific day of a year.

Examples

Data needs to be first partitioned into database shards based on the ID column. Then, table shards are created based on the days of a year in the values of the create_time column. This way, each day of a year can correspond to a physical table shard. In this case, you can execute the following DDL statement:

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;