MOD_HASH_CI
Application Scenarios
This algorithm applies if you want to route data to different database shards by user ID or order ID.
Instructions
- The sharding key must be CHAR, VARCHAR, INT, INTEGER, BIGINT, MEDIUMINT, SMALLINT, TINYINT, or DECIMAL (the precision can be 0).
- If you use a sharding key of the string type, do not convert the data type of its value in SQL statements. Changing the data type may cause a failure of routing calculation, and data will be routed to the default shard and cannot be found when you query it.
Data Routing
The data route depends on the remainder of the sharding key value divided by database or table shards. MOD_HASH is case-sensitive, but MOD_HASH_CI is not.
Calculation Method
Method 1: Use an Integer as the Sharding Key
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
NOTE:
The database routing result is rounded off to the nearest integer. |
Table shard: 16 % (8 x 3) = 16 Database shard: 16 / 3 = 5 |
Method 2: Use a String as the Sharding Key
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'.toUpperCase().hashCode()=64578 Database shard: 64578 % 8 = 2; Table shard: 64578 % 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
NOTE:
The database routing result is rounded off to the nearest integer. |
hash('abc') = 'abc'.toUpperCase().hashCode()=64578 Table shard: 64578% (8 x 3) = 18 Database shard: 18 / 3 = 6 |
Syntax for Creating Tables
- Assume that you use field ID as the sharding key to shard databases based on MOD_HASH_CI:
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);
- Assume that you use field ID as the sharding key to shard databases and tables based on MOD_HASH_CI:
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 4;
Precautions
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.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot