Tag Archives: Enum

Strange mysql sorting behaviour with enums

If you have an ENUM(‘Y’,'N’) field and you want to sort ASC on this field, mysql will show the Y values before the N values. It seems this is based on the order you defined them when creating your table… See the screenshot.

I’d like to add the explanation from mysql.com which can be found here.

ENUM values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, ENUM values are sorted according to their index numbers.) For example, ‘a’  sorts before ‘b’ for ENUM(‘a’, ‘b’), but ‘b’ sorts before ‘a’ for ENUM(‘b’, ‘a’). The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values. To prevent unexpected results, specify the ENUM list in alphabetic order.