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

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;

  4. FLUSH TABLES WITH READ LOCK;

  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

  6. UNLOCK TABLES;

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

  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!


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)

Tags:

Mysql

Alter Table

Recent Posts