This topic describes how to use the RIGHT_SHIFT function.
- The shard key must be an integer.
- The version of the PolarDB-X 1.0 instance must be 5.1.28-1320920 or later..
The RIGHT_SHIFT function performs a signed right shift on the value of the database shard key. The function then divides the resulting integer by the number of database or table shards and finds the remainder. Note that the value of the shard key must be an integer. You can specify the number of bits to shift by running a data definition language (DDL) statement.
The RIGHT_SHIFT function can produce more even hashing when the lower-digit parts of most shard key values are similar but the higher-digit parts vary greatly.
For example, assume you have the following four shard key values:
0x 0300 and
0x0400. The rightmost eight bits of each value are 0. Services may use the rightmost bits
as flags. In this case, using the remainder method on the original values can result
in less effective hashing. You can use
RIGHT_SHIFT (shardKey, 8) to shift the values of the keys eight bits to the right and obtain the following
0x04. These new values result in relatively even hashing. If a database is divided into
four shards, each value corresponds to one shard.
For example, assume that you are using the ID column as the shard key. You may want to shift the values of this column four bits to the right for hashing purposes. In this case, you can run the following statement:
create table test_hash_tb ( id int, name varchar(30) DEFAULT NULL, create_time datetime DEFAULT NULL, primary key(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by RIGHT_SHIFT(id, 8) tbpartition by RIGHT_SHIFT(id, 8) tbpartitions 4;