asked on

ALTER TABLE, enum

I would like to change the contents of an enum field in my mysql table.

FROM:
field1 enum('aaa','ccc','eee') default 'aaa'

TO:
field1 enum('aaa','bbb','ccc','eee') default 'aaa'

The table I am working with however is fully populated with data, and I'm worried that changing the enum field might corrupt the data in that field by changing the indicie values or something similar.

1) Does anyone know if changing the enum field (by just adding a new entry within it) will in any way corrupt the existing data?
2) What is the best method of updating the enum field to maintain the data? Would the following be sufficient and safe:
ALTER table1 modify field1 enum('aaa','bbb','ccc','eee') default 'aaa';

Please only answer if you are absolutely sure through experience or have a reference link to support your answer. I can't go on confident assumptions for this one. Hence my need to ask an expert. Thanks.

ASKER CERTIFIED SOLUTION

snoyes_jw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lth2h

Visit:
http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html
http://dev.mysql.com/doc/mysql/en/ENUM.html

Don't forget: ALWAYS BACKUP YOUR DATA!

One more note:
MySQL will attempt to maintain the string value of the data eventhough it stores enums as intergers.
So you can try:
ALTER TABLE table ADD new_column ...;
UPDATE table SET new_column = old_column + 0;
ALTER TABLE table DROP old_column;
Assuming that you kept the same order of your enum strings.