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.

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/

share|improve this question
2  
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
add comment

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
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
add comment

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.