Using the HASH function for database partitioning - PolarDB

The HASH function routes rows to database shards and table shards using a modulo operation on a partitioning key. Use HASH when your partitioning key values are numerically spread — such as user IDs or order IDs — to achieve even shard distribution.

HASH produces an even distribution only when the values in the partitioning key column are themselves evenly distributed.

Limits

The partitioning key must be an integer or a string.

How it works

HASH routing applies a modulo operation to the partitioning key. The calculation differs depending on whether the same key is used for both database shards and table shards.

Different keys for database shards and table shards

The database shard key value is divided by the number of database shards, and the remainder identifies the target shard.

Key type	Formula	Example
Integer	`key % D`	`HASH(8)` → `8 % D`
String	`hashcode(key).abs() % D`	`HASH("ABC")` → `hashcode("ABC").abs() % D`

Where D is the number of database shards.

Same key for database shards and table shards

When the same key is used for both database shards and table shards, the key value is divided by the total number of table shards across all database shards, and the remainder identifies the target table shard.

Example: 2 database shards, each with 4 table shards (8 table shards total).

Table shards	Database shard
0–3	Database shard 0
4–7	Database shard 1

Key value 15 routes as follows:

15 % (2 × 4) = 15 % 8 = 7  →  table shard 7  →  database shard 1

Use cases

Partition by user ID or order ID, where values are numerically spread across a wide range.
Use a string column as the partitioning key.

Example

The following Data Definition Language (DDL) statement creates a table partitioned by id across database shards, with no table-level sharding:

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);

A row with id = 15 routes to the database shard determined by 15 % D, where D is the number of database shards.