Solved

ALTER TABLE, enum

Posted on 2004-08-03
3
6,181 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

707 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