This topic describes how to use the YYYYWEEK function.

Limits

  • The partitioning key must be of the DATE, DATETIME, or TIMESTAMP type.
  • Before you use the YYYYWEEK 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 YYYYWEEK function can be used only to create a separate table shard for each week within a cycle.
  • When a week reoccurs after a cycle has completed, data from that week may be routed to the same table shard in the same database shard. For example, with a two-year cycle starting on the first week of 2012, data from the first week of 2014 may be routed to the same table shard as data from the first week of 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 week of the time value of the database shard key. Then divide by the number of database shards and find the remainder.

For example, the YYYYWEEK('2012-12-31 12:12:12') function is equivalent to (2013 × 54 + 1) % D, where D indicates the number of database shards. The calculation result indicates that the December 31, 2012 is in the first week of 2013.

Scenarios

The YYYYWEEK function is applicable to scenarios where data needs to be partitioned into database shards by year and week of the year. We recommend that you use this function with tbpartition by YYYYWEEK(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 week of the year.
  • Distribute data from the same week to the same table shard and ensure that each week 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 YYYYWEEK function can meet the preceding requirements. You require that each week within two years correspond to a table shard. Therefore, a total of 106 physical table shards must be created, because a year has up to 53 weeks (53 × 2 = 106). PolarDB-X 1.0 has eight database shards. Therefore, 14 physical table shards must be created in each database shard (106/8 = 13.25, rounded to 14). We recommend that the number of table shards be an integer multiple of the number of database shards.

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

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;