Changing a column name without re-creating the MySQL table

Question

Is there a way to rename a column in an InnoDB table without a major change?

The table is quite large, and I want to avoid significant downtime.

Answer 1

Renaming a column (with ALTER TABLE ... CHANGE COLUMN ), unfortunately, requires MySQL to execute a full copy of the table.

Check out pt-online-schema-change . This will help you make many types of ALTER changes in a table without locking the entire table for ALTER time. You can continue reading and writing to the original table when copying data to a new table. Changes are committed and applied to the new table through triggers.

Example:

 pt-online-schema-change h=localhost,D=databasename,t=tablename \ --alter 'CHANGE COLUMN oldname newname NUMERIC(9,2) NOT NULL' 

---

Update: MySQL 5.6 can perform some types of ALTER operations without rebuilding the table, and changing the column name is one of those that are supported as online changes. See http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html for an overview of what types of changes make or don't support this.

Answer 2

If there are no restrictions on it, you can change it without fuss, as far as I know. If you need to remove the restrictions first, change and add the restrictions back.

Answer 3

Changing a table with many rows can take a lot of time (although if the columns involved are not indexed, it can be trivial).

If you specifically want to avoid using the ALTER TABLE syntax created specifically for this purpose, you can always create a table with an almost identical structure (but a different name) and copy all the data into it, for example:

 CREATE TABLE `your_table2` ...; -- (using the query from SHOW CREATE TABLE `your_table`, -- but modified with your new column changes) LOCK TABLES `your_table` WRITE; INSERT INTO `your_table2` SELECT * FROM `your_table`; RENAME TABLE `your_table` TO `your_table_old`, `your_table2` TO `your_table`; 

For some ALTER TABLE queries, the above can be quite fast. However, for a simple change to the column name, this may be trivial. I could try to create an identical table and make changes to it to find out how much time you actually look.