Use the RANGE_HASH sharding function with two partitioning keys - PolarDB

RANGE_HASH is a dual-key partitioning function for PolarDB-X 1.0. Use it when data is sharded by two business keys but only one key is available at query time.

How it works

RANGE_HASH(COL1, COL2, N) routes each row to a shard by taking the last N characters of a partitioning key and computing the remainder after dividing the hash value by the number of database shards.

Parameter reference

Parameter	Type	Description
`COL1`	string or number	Primary partitioning key. Selected first when present in the query.
`COL2`	string or number	Fallback partitioning key. Selected when `COL1` is absent from the query.
`N`	integer	Number of trailing characters used for hash calculation. Both keys must have at least `N` characters, and their last `N` characters must be identical for any given row.

Routing logic

When a query arrives, PolarDB-X 1.0 selects the available key in this order:

Query provides	Key used for routing
`COL1` only	`COL1` — last `N` characters hashed
`COL2` only	`COL2` — last `N` characters hashed
Both `COL1` and `COL2`	`COL1` — last `N` characters hashed

In all cases, the hash value is divided by the shard count and the remainder determines the target shard.

Limitations

COL1 and COL2 must be the same data type: string or number.
Neither partitioning key can be modified after table creation.
Range queries on partitioning keys are not supported.
When inserting data, the last N characters of COL1 and COL2 must be identical.
String values must be at least N characters long.
Requires PolarDB-X 1.0 version 5.1.28-1320920 or later.

Example

The following Data Definition Language (DDL) statement creates an order table in a PolarDB-X 1.0 cluster with eight physical database shards. Orders are sharded by buyer_id and order_id, but queries may provide only one of these IDs.

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;

With N = 10, PolarDB-X 1.0 computes the hash from the last 10 characters of whichever key is present. A query filtering by buyer_id and a query filtering by order_id both route to a single shard, as long as the last 10 characters of each pair match at insert time.