Help Center/ Distributed Database Middleware/ User Guide (Kuala Lumpur Region)/ SQL Syntax/ DDL/ Sharding Algorithms/ MOD_HASH_CI
Updated on 2022-08-17 GMT+08:00
View PDF
Share

MOD_HASH_CI

MOD_HASH_CI is the same as MOD_HASH except that MOD_HASH_CI is case-insensitive.

For example, the MOD_HASH_CI algorithm gets the same routing result no matter the sharding key value is abcd or ABCD.

Instructions

The sharding key must be the numeric data type (INT, INTEGER, BIGINT, MEDIUMINT, SMALLINT, TINYINT, DECIMAL, or NUMERIC) or the string data type.

Data Routing

The data route generally depends on the remainder of the sharding key value divided by database shards. If the value is a string, convert the string into a hashed value and use the value to calculate remainders.

For example, MOD_HASH('6') is equivalent to 6%D. D is the number of shards.

If the same key is used for database and table sharding, the algorithm calculates the remainder by the total number of table shards.

For example, if there are 2 database shards and each shard has 4 table shards, table shards 0 to 3 are stored on shard 0, and table shards 4 to 7 are stored on database shard 1. If the sharding key value is 15, data is distributed to table shard 7 on database shard 1 according to 15% (2x4) = 7.

Calculation Method

Method 1: Use an Integer as the Sharding Key

Table 1 Required calculation methods

Condition

Calculation Method

Example

Database sharding key ≠ Table sharding key

Database routing result = Database sharding key value % Database shards

Table routing result = Table sharding key value % Table shards

Database shard: 16 % 8 = 0

Table shard: 16 % 3 = 1

Database sharding key = Table sharding key

Table routing result = Sharding key value % (Database shards x Table shards)

Database routing result = Table routing result / Table shards

Table shard: 16 % (8 x 3) = 16

Database shard: 16 / 3 = 5

Method 2: Use a String as the Sharding Key

Table 2 Required calculation methods

Condition

Calculation Method

Example

Database sharding key ≠ Table sharding key

Database routing result = hash(Database sharding key value) % Database shards

Table routing result = hash(Table sharding key value % Table shards

hash('abc') = 'abc'.hashCode()=96354

Database shard: 96354 % 8 = 2;

Table shard: 96354 % 3 = 0;

Database sharding key = Table sharding key

Table routing result = hash(Sharding key value) % (Database shards x Table shards)

Database routing result = Table routing result / Table shards

hash('abc') = 'abc'.hashCode()=96354

Table shard: 96354% (8 * 3) = 18

Database shard: 18 / 3=6

Syntax for Creating Tables

create table mod_hash_ci_tb (
    id int,
    name varchar(30) DEFAULT NULL,  
    create_time datetime DEFAULT NULL,
    primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
dbpartition by mod_hash_ci (ID)
tbpartition by mod_hash_ci (ID) tbpartitions 6;

Precautions

  • The sharding key and its value cannot be modified.
  • The MOD_HASH_CI algorithm is a simple way to find the remainder of the sharding key value divided by shards. This algorithm features even distribution of sharding key values to ensure even results.
Parent topic: Sharding Algorithms