Solved

ALTER TABLE, enum

Posted on 2004-08-03
3
6,168 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 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