23

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...

ALTER TABLE  `test`
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/

Improve this question
2
  • 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
    Commented 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.
    – RolandoMySQLDBA
    Commented Jan 24, 2012 at 17:12

2 Answers 2

Reset to default
18

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?

ALTER TABLE  `test`
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?

ALTER TABLE  `test`
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.

SUMMARY

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.

Improve this answer
5
  • 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
    Commented Jan 24, 2012 at 19:07
  • ENUMs are strings not integers : dev.mysql.com/doc/refman/5.0/en/enum.html
    – RolandoMySQLDBA
    Commented Jan 24, 2012 at 19:21
  • I agree ENUMs are strings but internally those are nor stored as strings isn't it?
    – Aalex Gabi
    Commented 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.
    – RolandoMySQLDBA
    Commented Jan 24, 2012 at 19:30
  • 7
    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
    Commented Oct 1, 2016 at 19:22
0

You can add to or remove values from ENUM lists with no problem (MySQL 5.7 and up), but attempting to simply rename a given value will result in data loss, unless an approach like MySQL Change a column ENUM value is followed.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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