This topic describes how to use the MMDD function.
- 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 DRDS instance must be 5.1.28-1320920 or later..
To obtain the table shard subscript, divide by the day in the time value of the partitioning key of a database shard.
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.
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
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;