How to Use String-Typed Columns as Partition Keys

  • by
You might have known that LIST, RANGE, and HASH partitions all refuse to accept VARACHAR column as the native partition key.
mysql> alter table locations partition by list(city) (
    ->  partition part_east values in ('New York', 'Philadelphia'),
    ->  partition part_west values in ('Los Angeles', 'San Jose')
    -> );
ERROR 1697 (HY000): VALUES value for partition 'part_east' must have type INT

You might also have tried to convert the string into integer to make LIST PARTITION to accept the column indirectly. But it also failed.
mysql> alter table locations partition by list(hex(city)) (
    ->  partition part_east values in (hex('New York'), hex('Philadelphia')),
    ->  partition part_west values in (hex('Los Angeles'), hex('San Jose'))
    -> );
ERROR 1564 (HY000): This partition function is not allowed

Unluckily, the function hex() is not allowed in partition. You may check the available functions for partition keys in this document:
MySQL :: MySQL 5.7 Reference Manual :: 18.6.3 Partitioning Limitations Relating to Functions

There's only a legal way to do it is LIST COLUMNS.
MySQL :: MySQL 5.7 Reference Manual :: 18.2.3.2 LIST COLUMNS partitioning
... and for columns of data types other than integer types to be used as partitioning columns; you can use string types, DATE, and DATETIME columns.
Let's see an example for LIST COLUMNS.
mysql> alter table locations partition by list columns(city) (
    ->  partition part_east values in ('New York', 'Philadelphia'),
    ->  partition part_west values in ('Los Angeles', 'San Jose')
    -> );
Query OK, 29530359 rows affected (15 min 2.73 sec)
Records: 29530359  Duplicates: 0  Warnings: 0

Leave a Reply

Your email address will not be published. Required fields are marked *