YYYYMM

This topic describes how to use the YYYYMM function.

Description

You can use the YYYYMM function to calculate hash values based on the years and months in the time values of a database shard key. Then, the function divides the hash values by the number of database shards to obtain the remainders. As a result, data is partitioned based on the remainders.

For example, the YYYYMM('2012-12-31 12:12:12') function is equivalent to (2012x12+12)%D. D specifies the number of database shards into which data is partitioned. The calculation result indicates that December 31, 2012 is the twelfth month of 2012.

Limits

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

• Before you use the YYYYMM function, determine the total number of physical table shards that are required. You can determine this number based on a cycle, such as two years. The YYYYMM function can be used only to create an independent table shard for each month within a cycle.

• When a month reoccurs after a cycle ends, data generated in the month may be partitioned into the same table shard in the same database shard. For example, after a two-year cycle, March 2014 corresponds to March 2012. The data generated in March 2014 may be partitioned into the same table shard as the data generated in March 2012. The table shard to which the data is partitioned is determined based on the number of table shards.

Scenarios

The YYYYMM function is suitable for scenarios in which data needs to be partitioned into database shards based on the year and the number of months in the year. We recommend that you use the YYYYMM function and the tbpartition by YYYYMM(ShardKey) clause together.

Examples

A Distributed Relational Database Service (DRDS) instance contains eight physical database shards. The following requirements must be met:PolarDB-X

• Data is partitioned into the database shards based on the year and number of months in the year.

• Data generated in the same month is partitioned into the same table shard. Each month within a cycle of two years corresponds to an independent table shard.

• A query is directly distributed to a specific physical table shard of a database shard if shard keys are specified in the query.

The YYYYMM function can meet the preceding requirements. If each month within two years needs to correspond to a table shard, a total of 24 physical table shards must be created. This is because a year has 12 months. The total number of physical table shards that must be created is calculated by using the following equation: 12 × 2 = 24. Three physical table shards must be created in each database shard because a PolarDB-X instance has eight database shards. The number of physical table shards that must be created by using the following equation: 24/8 = 3. To meet these requirements, execute the following DDL statement:

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;