The WEEK function routes each row to a table shard based on the day of the week in the shard key value. It extracts the weekday, divides it by 7 to get a remainder, and uses that remainder as the table shard subscript.
How it works
WEEK accepts a shard key of type DATE, DATETIME, or TIMESTAMP. For each row, it maps the weekday of the shard key value to a subscript and routes the row to the corresponding table shard. Each subscript indicates a day from Monday to Sunday in a week.
Limits
- The shard key must be of type DATE, DATETIME, or TIMESTAMP.
- WEEK can only be used for table sharding, not database sharding.
- Each database shard can have no more than 7 table shards, because a week has seven days.
Use cases
Use WEEK when your workload follows a clear weekly access pattern and you want to distribute table data evenly across the 7 days of the week. Each table shard corresponds to one day, Monday through Sunday.
Example
The following example partitions data into database shards by the name column using HASH, then partitions each database shard into 7 table shards by the day of the week in create_time.
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;