Skip to content
Home » MySQL » MySQL Partition by String Column

MySQL Partition by String Column

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, which 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 *