MySQL Enums have non-intuitive behaviour, why? This very helpful article explains some oddities about how MySQL Enums behave: http://melp.nl/2009/05/mysql-enums-and-booleans/

There is no discussiong about why that behaviour exists. Furthermore, in googling the subject I find only contradictory information and speculation, by 'bloggers' and other blind leading the blind.

Can someone please describe why MySQL Enums suffer from the behaviour outlined in the post. Here is one example of Enum oddities presented in the post, but there are many more:

mysql&gt; insert into t values('1'),('0');
mysql&gt; select b,b=1,b=0 from t;
+---+-----+-----+
| b | b=1 | b=0 |
+---+-----+-----+
| 1 |   0 |   0 |
| 0 |   1 |   0 |
+---+-----+-----+
2 rows in set (0.00 sec)
The SELECT in your example should be consistent with the INSERT with the way it uses quotes. The following works:

select b,b='1',b='0' from t;


Here's proof.

MySQL's enum documentation explains that an enum is an index of strings - every entry has both a numeric index and a string value. The SELECT query in your question is using the numeric index, and the one I've provided is using the strings as you inserted them.

Also noted in the documentation are that the first value always has an index of 1, and that the index 0 always resolves to an empty string (''). An enum of ('0','1','3','Banana','2') would therefore look like this:

Index = 'Value'
    0 = ''
    1 = '0'
    2 = '1'
    3 = '3'
    4 = 'Banana'
    5 = '2'


In the above example - as well as your original example - select b,b=1,b=0 from t; will look up the indexes 0 and 1, which resolve to the values '' and 0 respectively.

You will almost never need to use the indexes when working with enum data - make sure you use the string values, and put quotes around them every time.

As a general rule, don't use enum types to store numbers. The risk of confusion is extremely high, and if you really want to restrict the values, there are other appropriate tools, such as lookup tables with foreign keys.