MM

This topic describes how to use the MM function.

Description

You can use the MM function to divide the month in each time value of the database shard key by 12 to obtain the subscripts of table shards.

Limits

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

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

• When you use the MM function to partition data into table shards, make sure that each database shard contains 12 table shards or less. This is because a year has only 12 months.

Scenarios

The MM function is suitable for scenarios in which data needs to be partitioned into table shards by month. The name of a table shard indicates a specific month.

Examples

Data needs to be partitioned into database shards by user ID. Then, data in each database shard needs to be partitioned into physical table shards based on the months in the values of the create_time column. This way, each month can correspond to a physical table. In this case, you can execute the following DDL:

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