PolarDB:HASH

Note

The UNI_HASH functions perform simple modulo operations. The output of the UNI_HASH functions can be evenly distributed only when the values in the partitioning key column are evenly distributed.

Limits

Routing method

• If different partitioning keys are used to execute the HASH function for database shards and table shards, divide the value of the database shard key by the number of database shards and find the remainder. If the key value is a string, the string is first converted into a hash value and then used for route calculation. For example, HASH(8) is equivalent to 8 % D, where D indicates the number of database shards. HASH("ABC") is equivalent to hashcode("ABC").abs() % D.
• If the same partitioning key is used to execute the HASH function for database shards and table shards, divide the value of the partitioning key by the total number of table shards and find the remainder. Assume that two database shards are created, each database shard contains four table shards, table shards 0 to 3 are stored in database shard 0, and table shards 4 to 7 are stored in database shard 1. Based on this routing method, the key value 15 is distributed to table shard 7 in database shard 1. The equation is ((15 % (2*4) = 7)).

Scenarios

The HASH function can be used in the following scenarios:

• Partition databases by user ID or order ID.
• Use a string as the partitioning key.

Examples

Assume that you want to execute the HASH function to create non-partitioned tables in database shards based on the ID column. You can execute the following Data Definition Language (DDL) statement to create tables:

create table test_hash_tb (
    id int,
    name varchar(30) DEFAULT NULL,  
    create_time datetime DEFAULT NULL,
    primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by HASH(ID);