WEEK

This topic describes how to use the WEEK function.

Description

You can use the WEEK function to divide the day of a week in each time value of a shard key by 7 to obtain the remainders. This way, the subscripts for table sharding can be obtained.

Limits

• A shard key must be of the DATE, DATETIME, or TIMESTAMP type.

• The WEEK function can be used only for table sharding. This function cannot be used for database sharding.

Scenarios

The WEEK function is suitable for scenarios in which data needs to be partitioned into table shards by day of a week. The subscript of each table shard name indicates a day from Monday to Sunday in a week.

Examples

Data needs to be first partitioned into database shards based on the ID column. Then, data needs to be partitioned into table shards based on the days of a week in the values of the create_time column. Each day from Monday to Sunday in a week corresponds to a physical table shard. In this case, you can execute the following DDL statement:

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;

Note When you use the WEEK function to partition data into table shards, make sure that each database shard has no more than seven table shards. This is because a week has seven days.