• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6278
  • Last Modified:

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.
0
blinkie23
Asked:
blinkie23
1 Solution
 
snoyes_jwCommented:
I tried it.  Seems to be ok.  Still, be sure to back up your data first.

mysql> create table testenum (
    ->  id int auto_increment primary key,
    ->  theEnum enum('aaa','ccc','eee') default 'aaa'
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> insert into testenum (theEnum) values ('aaa'), ('eee'), ('ccc');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from testenum;
+----+---------+
| id | theEnum |
+----+---------+
|  1 | aaa     |
|  2 | eee     |
|  3 | ccc     |
+----+---------+
3 rows in set (0.01 sec)

mysql> alter table testenum modify column theEnum enum('aaa', 'bbb', 'ccc', 'ddd', 'eee') default 'aaa';
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from testenum;
+----+---------+
| id | theEnum |
+----+---------+
|  1 | aaa     |
|  2 | eee     |
|  3 | ccc     |
+----+---------+
3 rows in set (0.00 sec)
0
 
lth2hCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now