YYYYWEEK

This topic describes how to use the YYYYWEEK function.

Description

You can use the YYYYWEEK function to calculate hash values based on the years and the number of weeks of a year 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 YYYYWEEK('2012-12-31 12:12:12') function is equivalent to (2013x54+1)%D. D specifies the number of database shards into which data is partitioned. The calculation result indicates that December 31, 2012 is the first week of 2013.

Limits

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

• Before you use the YYYYWEEK 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 YYYYWEEK function can be used only to create an independent table shard for each week within a cycle.

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

Scenarios

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

Examples

A PolarDB-X instance contains eight physical database shards. The following requirements must be met:

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

• Data generated in the same week is partitioned into the same table shard. Each week 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 YYYYWEEK function can meet the preceding requirements. If each week within a cycle of two years needs to correspond to a table shard, a total of 106 physical table shards must be created. This is because a year has up to 53 weeks. The total number of physical table shards that must be created is calculated by using the following equation: 53 × 2 = 106. Fourteen physical table shards must be created in each database shard because a PolarDB-X instance has eight database shards. To obtain the number of physical table shards in each database shard, divide the total number of physical table shards that must be created by the number of database shards. Then, round the result up to the next nearest integer. In this case, the result is 14. This result is calculated by using the following equation: 106/8=13.25. We recommend that the number of table shards be an integer multiple of the number of database shards. To meet these requirements, execute the following DDL statement:

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