Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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> insert into t values('1'),('0');
mysql> 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)
share|improve this question
    
Without explicitly telling what the problem is this question is not a good fit for the site, I guess. Could you please outline it in a few sentences? More detailed explanation could still be left for the article you linked. –  dezso May 5 at 10:00
    
MySQL doesn't have real booleans. It uses "C-Style" booleans where 0 means "false" anything else means true. So the "boolean" expression b=1 returns a number - either 0 or 1 and that is then used as an index for the actual enum definition. –  a_horse_with_no_name May 5 at 10:55

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
I think the reason why many people try to store numbers as enums is the lack of check constraints in MySQL. I see this very often as a workaround for that. –  a_horse_with_no_name May 5 at 11:56
    
Thank you. Now it all makes sense: the author of that post, like most PHP devs (yes, I am among other things a PHP developer as well), does not understand datatypes. I'll be sure to pay special attention to this in the future. Thank you! –  dotancohen May 5 at 13:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.