mysql - Partitioning BY HASH using VARCHAR field

Question

In a given table, I am trying to apply a partitioning scheme where the field used is a VARCHAR (23), something like a Guid.

Ex: 5cb4932bfbca0c0010343e68

The idea is to create 4 partitions using hash partitioning. For this I believe that you have to use some function that converts the field into a value that always fits the partitioning clause, I don't know if this is possible in MySQL.

CREATE TABLE MyTable (
  Id char(36) NOT NULL,
  ApiId varchar(23) NOT NULL,
  Classification int(11) NOT NULL,
  PRIMARY KEY (Id)
)
PARTITION BY HASH(ApiId)
PARTITIONS 4;

question from:https://stackoverflow.com/questions/65945388/partitioning-by-hash-using-varchar-field

Answer 1

Yes, you need to supply a hash function which returns an integer. From HASH partitioning...

To partition a table using HASH partitioning, it is necessary to append to the CREATE TABLE statement a PARTITION BY HASH (expr) clause, where expr is an expression that returns an integer.

The simplest solution is to store your ID as an integer and convert it to a hex string as needed. This is faster than storing varchar and uses less memory.

Unfortunately your ID is also too large to fit into a bigint. It's about 92 bits. Instead, use varbinary(12) (and varbinary(16) for 128 bit UUIDs).

Then you need to write a hash function to turn that varbinary value into an integer for hashing. Unfortunately I don't know how to do that in MySQL.