Skip to content

MySQL Partition by String Column

  • MySQL

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 *