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;