Change column name without recreating the MySQL table

mysql alter
Ran picture Ran · Nov 17, 2011 · Viewed 7.7k times · Source

Is there a way to rename a column on an InnoDB table without a major alter?

The table is pretty big and I want to avoid major downtime.

Answer

Bill Karwin picture Bill Karwin · Nov 17, 2011

Renaming a column (with ALTER TABLE ... CHANGE COLUMN) unfortunately requires MySQL to run a full table copy.

Check out pt-online-schema-change. This helps you to make many types of ALTER changes to a table without locking the whole table for the duration of the ALTER. You can continue to read and write the original table while it's copying the data into the new table. Changes are captured 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 do some types of ALTER operations without rebuilding the table, and changing the name of a column is one of those supported as an online change. See http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html for an overview of which types of alterations do or don't support this.

Related questions

How to remove constraints from my MySQL table?

I want to remove constraints from my table. My query is: ALTER TABLE `tbl_magazine_issue` DROP CONSTRAINT `FK_tbl_magazine_issue_mst_users` But I got an error: #1064 - You have an error in your SQL syntax; check the …

Read More
How to add new column to MYSQL table?

I am trying to add a new column to my MYSQL table using PHP. I am unsure how to alter my table so that the new column is created. In my assessment table I have: assessmentid | q1 | q2 | q3 | q4 | …

Read More
How to move columns in a MySQL table?

Currently I am having the following MySQL table: Employees (empID, empName, department); I want to change the table to the following: Employees (empID, department, empName); How can this be done using ALTER statements? Note: I want to change only column …

Read More