Can I rename the values in a MySQL ENUM column in one query?

Suppose I have a Database table with an ENUM('value_one','value_two').
I want to change that to an ENUM('First value','Second value'). I an currently doing that as follows:

ALTER TABLE `table` MODIFY `column` ENUM('value_one','value_two','First value','Second value');
UPDATE `table` SET `column`='First Value' WHERE `column`='value_one';
UPDATE `table` SET `column`='Second Value' WHERE `column`='value_two';
ALTER TABLE `table` MODIFY `column` ENUM('First value','Second value');

Is there a more efficient way to do this, E.G. a way to accomplish this with a single ALTER TABLE statement?

The follolwing technique I am about to show you will require guts of steel.

Given the following criteria

• datadir is /var/lib/mysql

• table is mydb.mytb

• enum column called is called enum_col

• engine is MyISAM

Here is a death-defying crack at it:

-
CREATE TABLE mydb.mybt LIKE mydb.mytb;

-
ALTER TABLE mydb.mybt MODIFY enum_col ENUM('First value','Second value');

-
SET wait_timeout=86400; SET interactive_timeout=86400;

-
FLUSH TABLES WITH READ LOCK;

-
In a separate OS/SSH Session, swap the .frm files

• $ mv /var/lib/mysql/mydb/mytb.frm /var/lib/mysql/mydb/myxx.frm

• $ mv /var/lib/mysql/mydb/mybt.frm /var/lib/mysql/mydb/mytb.frm

• $ mv /var/lib/mysql/mydb/myxx.frm /var/lib/mysql/mydb/mybt.frm

-
UNLOCK TABLES;

-
DROP TABLE mydb.mybt;

That's it !!!

CAVEAT : I CANNOT TAKE CREDIT FOR THIS !

This technique comes from "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:


The technique we are about to demonstrate is unsupported,
undocumented, and may not work. Use it at your risk. We advise you to
back up you data first!

Give it a try ! (Please let us know how it turned out)

UPDATE 2011-10-05 17:49 EDT

If the table is MyISAM and you have enough space in production and a straight-up downtime window, try this:

-
service mysql restart --skip-networking

-
In a separate OS/SSH Session, make a copy of the table

• cp /var/lib/mysql/mydb/mytb.frm /var/lib/mysql/mydb/mytbplay.frm

• cp /var/lib/mysql/mydb/mytb.MYD /var/lib/mysql/mydb/mytbplay.MYD

• cp /var/lib/mysql/mydb/mytb.MYI /var/lib/mysql/mydb/mytbplay.MYI

INFORMATION_SCHEMA.TABLES will automatically detect the presence of the new table called mydb.mytbplay.

-
Perform the guts-of-steel algorithm on mydb.mytbplay

-
You test the integrity of mydb.mytbplay

If you are satisfied

-
ALTER TABLE mydb.mytb RENAME mydb.mytb_backup;

-
ALTER TABLE mydb.mytbplay RENAME mydb.mytb;

-
service mysql restart

Give it a try!

StackExchange Database Administrators Q#6547, answer score: 10