YYYYDD

Application Scenarios

This algorithm applies when data is routed to shards by the year and the day 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 day of the year specified in the sharding key value to calculate the hash value.

For example, YYYYDD('2018-12-31 12:12:12') is equivalent to (2018 x 366 + 365) % D. D is the number of shards.

2018-12-31 is the 365th day of 2018. In the formula, 366 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 366 + Day of the current year) % Database shards Table routing result = (yyyy x 366 + Day of the current year) % Table shards	Sharding key: 2012-12-31 Database shard: (2012 x 366 + 366) % 8 = 6 Table shard: (2012 x 366 + 366) % 3 = 0
Database sharding key = Table sharding key	Sharding key: yyyy-MM-dd Table routing result = (yyyy x 366 + Day of the current year) % (Database shards x Table shards) Database routing result = Table routing result / Table shards	Sharding key: 2012-12-31 Database shard: (2012 x 366 + 366) % (8 x 3) = 6 Database shard: 6 / 3 = 2

Syntax for Creating Tables

create table yyyydd_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 YYYYDD(create_time)
tbpartition by YYYYDD(update_time) tbpartitions 30;

Precautions

The sharding key and its value cannot be modified.
The YYYYDD algorithm does not apply if each day of a year corresponds to one shard.
Data of the same day in different years may be routed to the same shard.

Parent topic: Sharding Algorithms

Previous topic: YYYYMM

Next topic: YYYYWEEK