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?

2 Answers 2


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:

  1. CREATE TABLE mydb.mybt LIKE mydb.mytb;

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

  3. SET wait_timeout=86400; SET interactive_timeout=86400;


  5. 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

  7. DROP TABLE mydb.mybt;

That's it !!!


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:

  1. service mysql restart --skip-networking

  2. 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.

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

  2. You test the integrity of mydb.mytbplay

If you are satisfied

  1. ALTER TABLE mydb.mytb RENAME mydb.mytb_backup;

  2. ALTER TABLE mydb.mytbplay RENAME mydb.mytb;

  3. service mysql restart

Give it a try!

  • +1 for an ingenious answer! I will not, however, be giving this a try and letting you know how it turned out as the question refers to an upgrade script for all my customer's live, production databases ;-) I may, however, try this on a development environment just for fun. But with that warning I'd never run this in production!
    – Josh
    Oct 5, 2011 at 21:28

A simple solution would be:

1- add a new column:

ALTER TABLE `table` ADD `enum2` ENUM('First value', 'Second value') NOT NULL AFTER `enum`;

2- Copy the value of column to enum2 with replacements:

UPDATE `table` SET enum2=REPLACE(`column`, "value_one", "new value")

3- Drop column column, rename enum to column.

NOTE: this question back to 2011-10-05, my solution is valid for MYSQL 4.1 and newer (AFAIK)

  • 2
    While this sounds like a good solution (and not nearly as nerve-wracking as the accepted answer!), it's worth noting that step 3 does require at least one ALTER TABLE statement, and the OP was looking for something that only required one. Again, that said, it seems like a perfectly standard, solid solution.
    – RDFozz
    Nov 6, 2018 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.