This topic describes how to use the RANGE_HASH function.

Description

You can use the RANGE_HASH function to hash values based on the last N characters of each value of a shard key. Then, the function divides the hash values by the number of database shards to obtain the remainders. As a result, data is partitioned based on the remainders. N specifies the third parameter in the function.

For example, in the RANGE_HASH(COL1, COL2, N) function, COL1 is preferentially selected and the last N characters of each value in the COL1 column are used to calculate hash values. If COL1 does not exist, COL2 is selected.

Limits

  • The shard key for database sharding and the shard key for table sharding in a database must be of the same data type.
  • The two shard keys cannot be modified.
  • The shard keys do not support range queries.
  • When data is inserted, the last N characters of each value in one shard key must be the same as those in the other shard key.
  • A string must contain no less than N characters.
  • A shard key must be of the INTEGER or STRING type.

Scenarios

The RANGE_HASH function is suitable for scenarios in which two shard keys are required but only one shard key can be specified in query criteria.

Examples

A PolarDB-X instance has eight physical database shards. Data needs to be partitioned into database shards by buyer ID and order ID. However, only the buyer ID or the order ID can be specified in the query criteria. In this case, you can execute the following DDL statement: 

create table test_order_tb (  
    id int, 
    buyer_id varchar(30) DEFAULT NULL,  
    order_id varchar(30) DEFAULT NULL, 
    create_time datetime DEFAULT NULL,
    primary key(id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
    dbpartition by RANGE_HASH(buyer_id,order_id, 10) 
    tbpartition by RANGE_HASH (buyer_id,order_id, 10) tbpartitions 3;