PolarDB:STR_HASH

Limitations

• The partition key column must be of type CHAR or VARCHAR.

• STR_HASH parameters cannot be changed after the table is created.

• The PolarDB-X 1.0 instance must be version 5.3.5 or later.

Syntax

STR_HASH( shardKey [, startIndex, endIndex [, valType [, randSeed ] ] ] )

Use cases

STR_HASH is designed for scenarios where a string column (such as an order ID or user ID) is the partition key and point queries dominate the access pattern — for example, looking up a transaction or logistics order by ID.

When STR_HASH achieves precise routing:

The default HASH function computes a hash of the entire string value, which may map multiple distinct values to the same shard. STR_HASH lets you extract a known-numeric substring (such as the last three digits of an order ID) and treat it as an integer, guaranteeing that each distinct substring value maps to exactly one shard.

Example scenario: An internet finance application partitions data into database shards by year and month using YYYYMM, then partitions into table shards by order ID. The last three characters of each order ID are integers in the range 000–999. The requirement is that each distinct three-digit suffix routes to exactly one physical table shard.

-- Routing: int(order_id[-3:]) % 1024 → one physical table shard per suffix value
CREATE TABLE test_str_hash_tb (
    id INT NOT NULL AUTO_INCREMENT,
    order_id VARCHAR(30) NOT NULL,
    create_time DATETIME DEFAULT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
DBPARTITION BY YYYYMM(`create_time`)
TBPARTITION BY STR_HASH(`order_id`, -1, 3, 1) TBPARTITIONS 1024;

With 1,024 table shards and 1,000 distinct suffix values (000–999), each suffix routes to exactly one shard. The default HASH function cannot guarantee this because its hash may map multiple string values to the same integer, causing one shard to serve multiple suffix values.

Examples

The following examples use a table with order_id VARCHAR(32) as the partition key, partitioned into four database shards and eight table shards (four databases, two tables each).

Use the last four characters as an integer for routing:

-- Routing: int(order_id[-4:]) % 4 → database shard; % 2 → table shard
CREATE TABLE test_str_hash_tb (
    id INT NOT NULL AUTO_INCREMENT,
    order_id VARCHAR(32) NOT NULL,
    create_time DATETIME DEFAULT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
DBPARTITION BY STR_HASH(`order_id`, -1, 4, 1)
TBPARTITION BY STR_HASH(`order_id`, -1, 4, 1) TBPARTITIONS 2;

Use characters at positions 2–6 (third through seventh) as a string for routing:

-- Routing: hash(order_id[2:7]) % 4 → database shard; % 2 → table shard
CREATE TABLE test_str_hash_tb (
    id INT NOT NULL AUTO_INCREMENT,
    order_id VARCHAR(32) NOT NULL,
    create_time DATETIME DEFAULT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
DBPARTITION BY STR_HASH(`order_id`, 2, 7)
TBPARTITION BY STR_HASH(`order_id`, 2, 7) TBPARTITIONS 2;

Use the first five characters as a string for routing:

-- Routing: hash(order_id[:5]) % 4 → database shard; % 2 → table shard
CREATE TABLE test_str_hash_tb (
    id INT NOT NULL AUTO_INCREMENT,
    order_id VARCHAR(32) NOT NULL,
    create_time DATETIME DEFAULT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
DBPARTITION BY STR_HASH(`order_id`, 5, -1)
TBPARTITION BY STR_HASH(`order_id`, 5, -1) TBPARTITIONS 2;

FAQ

What is the difference between STR_HASH(order_id) and HASH(order_id)?

Both functions use a string column as the partition key and compute a hash to determine the target shard. The key differences are:

• STR_HASH lets you extract a substring before hashing, so you can target a known segment of the key value (such as the last four digits of an order ID).

• STR_HASH uses the UNI_HASH algorithm for string-based routing. HASH performs a simpler modulo operation on the full string hash.

• When valType=1, STR_HASH treats the substring as an integer and routes deterministically — each distinct integer maps to exactly one shard. HASH does not offer this guarantee.

Use STR_HASH when you need precise one-to-one routing between a known substring value and a shard. Use HASH for general-purpose string partitioning when substring extraction is not required.