CODEWITHSUNDEEP
mysqlalter
Ran
Ran

Reputation: 3523

Change column name without recreating the MySQL table

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.

Upvotes: 7

Views: 8903

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562310

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.

Upvotes: 9

TehShrike
TehShrike

Reputation: 10074

Altering a table with many rows can take a long time (though if the columns involved are not indexed, it may be trivial).

If you specifically want to avoid using the ALTER TABLE syntax created specifically for that purpose, you can always create a table with almost the exact same structure (but different name) and copy all the data into it, like so:

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 a bit faster. However, for a simple column name change, it could be trivial. I might try creating an identical table and performing the change on it in order to see how much time you're actually looking at.

Upvotes: 0

Kim
Kim

Reputation: 1804

If there aren't any constraints on it, you can alter it without a hassle as far as I know. If there are you'll have to remove the constraints first, alter and add the constraints back.

Upvotes: 0

Related Questions