How partition by custom hash function in MySql?

Question

I am trying to create table like this:

CREATE TABLE `my_table` (
     `query_id` varchar(255) NOT NULL,
     PRIMARY KEY (`query_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
PARTITION BY hash (TO_DAYS(STR_TO_DATE('query_id', '%Y%m%d'))) partitions 365;

And got exception: [HY000][1564] This partition function is not allowed

query_id format is like this 20250218_175459_23233_v3b6p. I want to grab 20250218 as date or number and partition by day.

Any idea how to do this without any new columns? Is this even supported with MySql?

Answer 1

No, you can't use any arbitrary expression as a partitioning expression.

https://dev.mysql.com/doc/mysql-partitioning-excerpt/en/partitioning-limitations-functions.html lists the subset of functions allowed in a partitioning expression. STR_TO_DATE() is not in the list.

Nor can you add a generated column and partition by that column, because of another rule:

CREATE TABLE `my_table` (
     `query_id` varchar(100) NOT NULL,
     PRIMARY KEY (`query_id`),
    query_id_p DATE AS (STR_TO_DATE(query_id, '%Y%m%d'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH (TO_DAYS(query_id_p)) partitions 365;

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).

Read https://dev.mysql.com/doc/mysql-partitioning-excerpt/en/partitioning-limitations-partitioning-keys-unique-keys.html for details on this error.

The following may be the closest to what you want that actually works:

CREATE TABLE `my_table` ( 
     `query_id_date` DATE NOT NULL,
     `query_id_remainder` VARCHAR(100) NOT NULL,
     PRIMARY KEY (`query_id_date`, `query_id_remainder`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH (TO_DAYS(query_id_date)) partitions 365;

This requires creating another column to separate the date portion from the rest of your primary key, but it uses a permitted function (TO_DAYS()) for the partitioning.

All this being said, you might not benefit from using partitioning at all. Simple lookup by primary key is probably sufficient for optimizing most queries, and if your query is doing some other type of lookup, then the partitioning won't help anyway.

P.S.: Use utf8mb4, not utf8mb3.