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.

Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled.

Is it better for this column's data type to be an int/bool (1 or zero) or to use ENUM with the values being enabled and disabled? What are the advantages or disadvantages?

Let's say instead of just two valid status's, you have 4 or 10 or even more? Do the advantages and disadvantages sway to one side or the other as the number of required values increases?

share|improve this question
1  
This question should be bookmarked by all MySQL Developers because it can become a source of either heartbreak or triumph. +1 !!! –  RolandoMySQLDBA Oct 19 '11 at 16:32

3 Answers 3

up vote 21 down vote accepted

I found a very bizarre but informative article about 8 reasons why one should not use ENUM.

Even without the article, I know

share|improve this answer
1  
Even though the other answer(s) in this thread are very informative, I'm marking this one as the answer because of the very helpful article. –  Jakobud Oct 19 '11 at 16:44
    
@Jakobud : Thank you, kind sir !!! –  RolandoMySQLDBA Oct 19 '11 at 16:47
3  
+1 You can't reuse the member-list of an ENUM column in other tables. ENUM has limited portability to other DBMS. –  onedaywhen Oct 20 '11 at 8:24

Well, first off we have the storage requirements. I'm going to assume you meant a tinyint (instead of int).

  • ENUM takes 1 byte (if under 255 values) or 2 bytes (up to maximum of 65,535
  • TinyInt takes 1 byte (maximum of 255 values)
  • Boolean is a synonym for TinyInt

So, on the surface, they're all the same. ENUM does take up some metadata for the string value associated with it though (older src)

I would say as you add more values though, any advantage starts to swing away from ENUM. Especially if you add the values after the table is already in use, because you have to alter the table structure to accommodate.

What is the advantage of using an ENUM? A string representation of what the value means. That's it, as far as I'm concerned. How valuable that is depends on your application.

share|improve this answer
1  
+1 for mentioning the only real advantage : String representation. Everything else is a wash going forward. –  RolandoMySQLDBA Oct 19 '11 at 16:26

I find ENUM is a short form definition of a code table. Its main advantage is that it avoids the code required to join and show the code description. It also eases setting the values if they arrive in string form.

I find it has the following disadvantages:

  • No capability for additional metadata about the code.
  • Difficult to add or disable values. (Disabling codes can be accomplished with a trigger and expiry field.)
  • I18n in the database can not be done.
  • Not reusable across tables.
share|improve this answer

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.