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 '12 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. – RolandoMySQLDBA Jan 24 '12 at 17:12

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 '12 at 19:07
  • ENUMs are strings not integers : dev.mysql.com/doc/refman/5.0/en/enum.html – RolandoMySQLDBA Jan 24 '12 at 19:21
  • I agree ENUMs are strings but internally those are nor stored as strings isn't it? – Aalex Gabi Jan 24 '12 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. – RolandoMySQLDBA Jan 24 '12 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 '16 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.