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

YYYYMM

Application Scenarios

This algorithm applies when data is routed to shards by year and month.

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. Enter the year and month into the hash function to obtain the hash value.

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

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 12 + MM) % Database shards

Table routing result = (yyyy x 12 + MM) % Table shards

Sharding key: 2012-11-20

Database shard: (2012 x 12 + 11) % 8 = 3

Table shard: (2012 x 12 + 11) % 3 = 2

Database sharding key = Table sharding key

Sharding key: yyyy-MM-dd

Table routing result = (yyyy x 12 + MM) % (Database shards x Table shards)

Database routing result = Table routing result / Table shards

Sharding key: 2012-11-20

Table shard: (2012 x 12 + 11) % (8 x 3) = 11

Database shard: 11 % 3 = 2

Syntax for Creating Tables

create table yyyymm_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 YYYYMM(create_time)
tbpartition by YYYYMM(update_time) tbpartitions 12;

Precautions

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