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?

  • 6
    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

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

Even without the article, I know

  • 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. – Jake Wilson Oct 19 '11 at 16:44
  • 6
    +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
  • What could I have to do if I have a field has relations with other records of the same table? For example, suppose units model for a shop items where each record should have property for its related units composition. i.e Ton, Kg, gm – SaidbakR Jan 12 '16 at 12:52
  • Nothing bizarre about that article - it's great! I think it was this post that put me on to why they're bad! I gave it a +1! – Vérace Oct 24 '17 at 16:52
  • 1
    The article suggests to use a good old table, instead of ENUM types. Should we add this to the answer? – Utku Nov 24 '17 at 15:51

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.

  • 4
    +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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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