I wasn't sure if changing ENUM() list isn't possible so I made a test. In MySQL v5.1.58 I made a test InnoDB table that contains one field called 'bool' of type ENUM('yes', 'no').

Then I executed...

CHANGE  `bool`  `bool` ENUM(  'yes',  'no',  'maybe' )
CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

...and it worked.

Have I done anything wrong? Is it dependent on db engine?
Why everybody says changing a ENUM() list isn't possible? eg. here http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/

  • 3
    The article you mentioned doesn't say it's impossible; it says that changing member list is expensive because the engine does full table scan.
    – a1ex07
    Jan 24, 2012 at 17:08
  • I mentioned your link back in October about ENUMs (dba.stackexchange.com/a/6966/877). Additionally, I posted a reference on how to do this in MyISAM (dba.stackexchange.com/a/6548/877). InnoDB is out of the question in this instance. Jan 24, 2012 at 17:12

1 Answer 1


As long as the table is empty, there is no problem. As long as new values for ENUM is appended and not renamed given a populated table, again no problem.

The ENUM you redefined in your question actually kept the original internal values for yes and no as the test table last remembered it.

The following applies to populated tables:

What about this?

CHANGE  `bool`  `bool` ENUM(  'no',  'yes',  'maybe' )
CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

Now you have a problem. The ENUM values in a fully populated table would have their internal values reversed so that yes is now no and no is now yes.

What about this?

CHANGE  `bool`  `bool` ENUM(  'maybe', 'no',  'yes' )
CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

Big Problem. In a populated table, yes is now maybe. New rows inserted with yes are disconnected from the previous yes rows because they now mean maybe.


There are very high-risk, bait-and-switch techniques to do this very quickly in MyISAM. I would strongly advise against doing this in InnoDB because of its tablespace id interaction with ibdata1.

  • So internally those are stored as int based on the order in the ENUM(). For instance ENUM('yes', 'no', 'maybe') stores internally 0 for 'yes', 1 for 'no', 2 for 'maybe'. I imagine that the metadata of the table is like ENUM('yes', 'no', 'maybe') instead of ENUM('yes' => 0, 'no' => 1, 'maybe' => 2). Is it true?
    – Aalex Gabi
    Jan 24, 2012 at 19:07
  • ENUMs are strings not integers : dev.mysql.com/doc/refman/5.0/en/enum.html Jan 24, 2012 at 19:21
  • I agree ENUMs are strings but internally those are nor stored as strings isn't it?
    – Aalex Gabi
    Jan 24, 2012 at 19:25
  • 1
    You are correct in this. In the link I provided ,there is a mapping of string to integer as metadata. Look for this phrase : For example, a column specified as ENUM('one', 'two', 'three') can have any of the values shown here. The index of each value is also shown. and the value/index map is conceptualized. Thus, there would be an ENUM value in a table associated with internal index number. Rearranging the strings will rearrange the metadata indexing. This does not bode well for a populated table when redefining an ENUM. Jan 24, 2012 at 19:30
  • 3
    At least for MariaDB/InnoDB, I can tell this is not valid anymore. Changing the middle of ENUM, as long as there are no records with the values being removed/changed, the other values should remain as they are. The only advantage is that it has to rebuild the table.
    – Nuno
    Oct 1, 2016 at 19:22

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.