Range

Application Scenarios

This algorithm applies to routing data in different ranges to different shards. Less-than signs (<), greater-than signs (>), and BETWEEN ... AND ... are frequently used in SQL queries.

Instructions

A sharding key can only be an integer, a date, or used in combination with a date function. If a date function is used, the sharding key must be of the DATE, DATETIME, or TIMESTAMP type.

A sharding key is generally the table field or the table field+date function. If the sharding key is the table field+date function, the table field must be DATE, DATETIME, or TIMESTAMP. The date function applies to routing data to different shards by year, month, week, day, or a combination of them.

Data Routing

Data is routed to different shards by the sharding key value based on algorithm metadata rules.

Calculation Method

Method 1: Use an Integer as the Sharding Key

**Table 1** Required calculation methods
Condition	Calculation Method	Example
Integer sharding keys	Database routing result: Data is routed to different shards based on the sharding key and the preset metadata range. Table routing result: Data is routed to different sharded tables based on the sharding key and the preset metadata range.	Data is routed to shard1 if the sharding key value is 3 and the preset metadata range is 3–4. Data is routed to shard2 if the sharding key value is 5 and the preset metadata range is 5–6.

Method 2: Use a Date as the Sharding Key

**Table 2** Supported date functions
Date Function	Calculation Method	Example
year()	year(yyyy-MM-dd)=yyyy	year('2019-10-11')=2019
month()	month(yyyy-MM-dd)=MM	month('2019-10-11')=10
weekofyear()	weekofyear(yyyy-MM-dd)=Week of the current year	weekofyear ('2019-10-11')=41
day()	day(yyyy-MM-dd)=Day of the current month	day ('2019-10-11')=11

**Table 3** Required calculation methods
Condition	Calculation Method	Example
Date sharding key	Database routing: Data is routed to different database shards based on the date function (database sharding key value) and the preset metadata range. Table routing: Data is routed to different table shards based on the date function (sharding key value) and the preset metadata range.	Data is routed to shard4 based on the metadata range 9–10 when the sharding key value is 10: month(2019-10-11)=10 belongs to 9–10=4.

Syntax for Creating Tables

create table range_tb(
    id int,
    name varchar(30) DEFAULT NULL,
    create_time datetime DEFAULT NULL,
    primary key(id)
    )
dbpartition by range(id)
{     
    1-2=0,
    3-4=1, 
    5-6=2, 
    7-8=3, 
    9-10=4, 
    11-12=5,
    13-14=6,
    default=7 
};