?
Solved

ALTER TABLE, enum

Posted on 2004-08-03
3
Medium Priority
?
6,233 Views
Last Modified: 2012-05-05
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
Comment
Question by:blinkie23
2 Comments
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 300 total points
ID: 11715918
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
 
LVL 1

Expert Comment

by:lth2h
ID: 11729508
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month15 days, 15 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question