This topic describes how to use the YYYYDD function.
- The partitioning key must be of the DATE, DATETIME, or TIMESTAMP type.
- Before you use the YYYYDD 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 YYYYDD function can be used only to create a separate table shard for each day within a cycle.
- When a date reoccurs after a cycle has completed, data from that date may be routed to the same table shard in the same database shard. For example, with a two-year cycle starting on March 1, 2012, data from March 1, 2014 may be routed to the same table shard as data from March 1, 2012. The specific table shard to which the data is routed depends on the number of table shards.
- The version of the DRDS instance must be 5.1.28-1320920 or later..
Calculate the hash value based on the year and day of the time value of the database shard key. Then divide by the number of database shards and find the remainder.
For example, the
YYYYDD('2012-12-31 12:12:12') function is equivalent to
(2012 × 366 + 366) % D, where D indicates the number of database shards. The calculation result indicates
that December 31, 2012 is the 366th day of 2012.
The YYYYDD function is applicable to scenarios where data needs to be partitioned
into database shards by year and day of the year. We recommend that you use this function
tbpartition by YYYYDD(ShardKey).
Assume that a DRDS instance has eight physical database shards and that you have the following requirements:
- Partition data into the database shards by year and day of the year.
- Distribute data from the same week to the same table shard and ensure that each day 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 YYYYDD function can meet the preceding requirements. You require that each day within two years correspond to a table shard. Therefore, a total of 732 physical table shards must be created, because a year has up to 366 days (366 × 2 = 732). DRDS has eight database shards. Therefore, 92 physical table shards must be created in each database shard (732/8 = 91.5, rounded to 92). 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_yyyydd_tb ( id int, name varchar(30) DEFAULT NULL, create_time datetime DEFAULT NULL, primary key(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by YYYYDD(create_time) tbpartition by YYYYDD(create_time) tbpartitions 92;