This topic describes how to use the MM function.

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 MM function to partition data into table shards, ensure that each database shard has no more than 12 table shards. This is because a year has 12 months.
  • The version of the DRDS instance must be 5.1.28-1320920 or later..

Routing method

To obtain the table shard subscript, divide by the month of the year in the time value of the database shard key.

Scenarios

The MM function is applicable to scenarios where data needs to be partitioned into table shards by month. The name of a table shard indicates a specific month.

Examples

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

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;