Solved

ALTER TABLE, enum

Posted on 2004-08-03
3
6,157 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
3 Comments
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 75 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now