PolarDB:WEEK

Limits

• The partitioning key must be of the DATE, DATETIME, or TIMESTAMP type.
• This function can be used only for partitioning data into table shards, not into database shards.
• When you use the WEEK function to partition data into table shards, ensure that each database shard has no more than seven table shards. This is because a week has seven days.
• The version of the PolarDB-X 1.0 instance must be 5.1.28-1320920 or later..

Routing method

To obtain the table shard subscript, divide by the day of the week in the time value of the database shard key.

Scenarios

The WEEK function is applicable to scenarios where data needs to be partitioned into table shards by each day of a week. The name subscript of a table shard indicates a specific day of a week, from Monday to Sunday.

Examples

Assume that you want to partition data into database shards by user ID and then create a physical table shard for each day of the week (Monday to Sunday) based on the create_time column. You can use the following Data Definition Language (DDL) statement to create tables:

create table test_week_tb (    
    id int, 
    name varchar(30) DEFAULT NULL,  
    create_time datetime DEFAULT NULL,
    primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
dbpartition by HASH(name) 
tbpartition by WEEK(create_time) tbpartitions 7;