Help Center/ Distributed Database Middleware/ User Guide (Kuala Lumpur Region)/ SQL Syntax/ DDL/ Sharding Algorithms/ YYYYWEEK
Updated on 2022-08-17 GMT+08:00
View PDF
Share

YYYYWEEK

Application Scenarios

This algorithm applies when data is routed to shards by the year and the week of the year specified in the sharding key value.

Instructions

The data type of the sharding key value is DATE, DATETIME, or TIMESTAMP.

Data Routing

The data route depends on the remainder of the hash value divided by the number of shards or sharded tables. Use the hash function and enter the year and the week of the year specified in the sharding key value to calculate the hash value.

For example, YYYYWEEK('2018-12-31 12:12:12') is equivalent to (2019 x 54 + 1) % D. D is the number of shards.

2018-12-31 is the first week of 2019. In the formula, 54 is a fixed value.

Calculation Method

Table 1 Required calculation methods

Condition

Calculation Method

Example

Database sharding key ≠ Table sharding key

Sharding key: yyyy-MM-dd

Database routing result = (yyyy x 54 + Week of the current year) % Database shards

Table routing result = (yyyy x 54 + Week of the current year) % Table shards

Sharding key: 2012-12-31

Database shard: (2013 x 54 + 1) % 8 = 7

Table shard: (2013 x 54 + 1) % 3 = 1

Database sharding key = Table sharding key

Sharding key: yyyy-MM-dd

Table routing result = (yyyy x54 + Week of the current year) % (Database shards x Table shards)

Database routing result = Table routing result / Table shards

Sharding key: 2012-12-31

Database shard: (2013 x 54 + 1) % (8 x 3) = 7

Database shard: 7 / 3 = 2

Syntax for Creating Tables

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

Precautions

  • The sharding key and its value cannot be modified.
  • The YYYYWEEK algorithm does not apply if each week of a year corresponds to one shard.
  • Data of the same week in different years may be routed to the same shard.
Parent topic: Sharding Algorithms