Updated on 2023-10-09 GMT+08:00

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

The 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 DATE, DATETIME, or TIMESTAMP.

Data Routing

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

Metadata needs to be set when a table is created. For example, if there are eight shards in one schema, the metadata range can be 1-2=0, 3-4=1, 5-6=2, 7-8=3, 9-10=4, 11-12=5, 13-14=6, and default=7. Data is routed to shards by the sharding key value based on the range.

Calculation Method

Method 1: Use an Integer as the Sharding Key

Table 1 Required calculation methods when the sharding key is the integer data type

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.

Data is routed to shard1 if the sharding key value is 3 and the preset metadata range is 3–4.

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 number of the current year

NOTE:

For the definition of the week number in a year, see WEEKOFYEAR(date).

weekofyear('2019-10-11')=41

day()

day(yyyy-MM-dd)=Day number of the current month

day('2019-10-11')=11

Table 3 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.

Data is routed to shard 4 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 
};

Precautions

None