Das Journal

Mindspace landfill of software developer Armin Beširović

Sorting MySQL ENUM values

Mar 10, 2020

MySQL ENUM values are sorted in the order defined in the ENUM, for example, a column defined like this:

order ENUM('third', 'first', 'second') NULL;

would sort in that order. If you want to sort by the lexicographical value of the ENUM you can either CAST() it to char or use CONCAT (which accomplishes the same thing):

ORDER BY CAST(order AS CHAR);
ORDER BY CONCAT(order);

Another option is to simply modify the ENUM so the values are sorted, this becomes then the ascending order for sorting them:

ALTER TABLE whatever MODIFY COLUMN order ENUM('first', 'second', 'third);

Not they would be sorted correctly.

Also, the values in your rows are not the ENUM indexes but (apparently) an identifier of the ENUM value. This means that the above ALTER query will not modify your data.