STR_HASH

Syntax

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

Only shardKey is required. Omitting all optional parameters routes rows by hashing the full shard key value as a string.

Parameters

Limits

• The shard key must be CHAR or VARCHAR. Numeric columns are not supported.

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

• String routing is case-sensitive: Order001 and order001 route to different shards.

Use cases

Precise routing by a fixed string format

Use STR_HASH when the shard key follows a fixed format and you need each physical table shard to correspond to exactly one shard key value range. A typical example: an order management system that shards by year-month (using YYYYMM) for database partitioning, then shards table partitions by the last three digits of each order ID—which always fall in the range 000–999.

CREATE TABLE orders (
    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;

This extracts the last three characters of order_id, converts them to an integer (valType=1), and applies a modulo operation across 1,024 table shards. Each shard maps to exactly one three-digit suffix—something the default HASH function cannot guarantee, because its hashCode may map different strings to the same integer.

String shard keys with point queries

When queries always filter on the exact shard key value (no range conditions), STR_HASH routes efficiently for any CHAR or VARCHAR column—such as transaction IDs, logistics tracking numbers, or user identifiers.

Examples

All examples use order_id VARCHAR(32) as the shard key, targeting 4 database shards and 8 table shards (2 table shards per database shard).

Route by the last 4 characters as an integer

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;

Route by characters 3 through 7 (positions 2–7) as a string

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;

Route by the first 5 characters as a string

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