This topic describes how to use the YYYYMM function.

Limits

  • The partitioning key must be of the DATE, DATETIME, or TIMESTAMP type.
  • Before you use the YYYYMM function, you must determine the total number of physical table shards required. This number can be determined based on a cycle, such as 2 years. The YYYYMM function can be used only to create a separate table shard for each month within a cycle.
  • When a month reoccurs after a cycle has completed, data from that month may be routed to the same table shard in the same database shard. For example, with a two-year cycle starting in March 2012, data from March 2014 may be routed to the same table shard as data from March 2012. The specific table shard to which the data is routed depends on the number of table shards.
  • The version of the PolarDB-X 1.0 instance must be 5.1.28-1320920 or later..

Routing method

Calculate the hash value based on the year and month of the time value of the database shard key. Then divide by the number of database shards and find the remainder.

For example, the YYYYMM('2012-12-31 12:12:12') function is equivalent to (2012 × 12 + 12) % D, where D indicates the number of database shards. The calculation result indicates that December 31, 2012 is in the 12nd month of 2012.

Scenarios

The YYYYMM function is applicable to scenarios where data needs to be partitioned into database shards by year and month of the year. We recommend that you use this function with tbpartition YYYYMM(ShardKey).

Examples

Assume that a PolarDB-X 1.0 instance has eight physical database shards and that you have the following requirements:

  • Partition data into the database shards by year and month of the year.
  • Distribute data from the same month to the same table shard and ensure that each month within two years corresponds to a separate table shard.
  • Directly distribute a query by partitioning key of database shards and table shards to a specific physical table shard of a database shard.

The YYYYMM function can meet the preceding requirements. You require that each month within two years correspond to a table shard. Therefore, a total of 24 physical table shards must be created, because a year has 12 months (12 × 2 = 24).PolarDB-X 1.0 has eight database shards. Therefore, three physical table shards must be created in each database shard (24/8 = 3).

You can use the following Data Definition Language (DDL) statement to create tables:

create table test_yyyymm_tb (    
        id int, 
        name varchar(30) DEFAULT NULL,  
    create_time datetime DEFAULT NULL,
    primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
dbpartition by YYYYMM(create_time)
tbpartition by YYYYMM(create_time) tbpartitions 3;