Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I compare MySQL enums?

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.

like image 453
DisgruntledGoat Avatar asked Nov 16 '08 23:11

DisgruntledGoat


2 Answers

You can use status+0 to return the index of the ENUM, starting from 1.

Refer http://serghei.net/docs/database/mysql/doc/E/N/ENUM.html

like image 190
felixlaumon Avatar answered Sep 30 '22 04:09

felixlaumon


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.

like image 21
Bill Karwin Avatar answered Sep 30 '22 02:09

Bill Karwin