views:

945

answers:

3

I have an enumeration: ENUM( 'alpha', 'beta', 'gamma', 'delta', 'omega' )

If I sort my table by this column I get them in the correct order defined above.

However, I can't find a way to select a subset of these, e.g. everything before delta. Using WHERE status < 'delta' only returns alpha and beta, not gamma. It seems MySQL uses a string comparison, not enum index comparison.

I could use the index numbers - i.e. WHERE status < 4 - but it's a bit of a code smell (magic numbers) and may break if I insert new values into the enumeration.

+1  A: 

You can use FIELD(column, "string1", "string2", ...) to find rows with any particular subset of possible ENUM values.

SELECT * FROM `table` WHERE FIELD(`enum_column`, "alpha", "delta", "et cetera");

If you want to use the range version, you can use FIND_IN_SET("needle", "hay,stack") to return the index but you'll have to extract the ENUM list out of the table definition first with another query.

Kendrick Erickson
+3  A: 

You're trying to use data-manipulation methods on metadata, and this is bound to be awkward.

This is a good reason to replace the ENUM with a foreign key to a lookup table. Then you can use conventional data-manipulation techniques.

Bill Karwin
A: 

just came across the same issue. if you want to sort a enum field you have to cast it to a string type first (category is my enum field in the example):

SELECT CONVERT(category USING utf8) as str_category FROM example GROUP BY str_category ORDER BY str_category

eazy!

carpool guy
This sounds like the opposite of my question. I wanted the ordering to be numeric, which it does fine. My problem was with the WHERE clause. Though perhaps there's a way to convert the ENUM field to a numerical value and use that? Doesn't really matter to me now, I asked this nearly a year ago and I don't even remember what it was for...
DisgruntledGoat