CODEWITHSUNDEEP
mysqlalter
demircan
demircan

Reputation: 241

Change mysql field name in a huge table

I have a table with 21 million row.I have to change one of the row name.When I try with the query "alter table company change id new_id int(11)"; query never ends.

Is there a simple way to change big mysql table's field name?

Upvotes: 9

Views: 6634

Answers (5)

Bill Karwin
Bill Karwin

Reputation: 562310

Use pt-online-schema-change to make long-running ALTER TABLE changes, without locking yourself out of the table.

http://www.percona.com/doc/percona-toolkit/pt-online-schema-change.html

It usually takes longer to complete the change, but you can still read from and write to the table while it's doing its work.

You can also listen to a webinar from the author, talking about how it works and some of its caveats:

http://www.percona.com/webinars/2012-05-02-zero-downtime-schema-changes-in-mysql

Upvotes: 5

demircan
demircan

Reputation: 241

The less time consuming method to change name of a field in a huge Mysql table is to :

CREATE TABLE new_big_table SELECT id as new_id,field1,field2,field3 FROM big_table.

Upvotes: 1

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

You could simply do the following:

MyISAM table

CREATE TABLE big_table_new LIKE big_table;
ALTER TABLE big_table_new MODIFY COLUMN id INT(11);
ALTER TABLE big_table_new DISABLE KEYS;
INSERT INTO big_table_new SELECT * FROM big_table;
ALTER TABLE big_table_new ENABLE KEYS;
ALTER TABLE big_table RENAME big_table_bak;
ALTER TABLE big_table_new RENAME big_table;

InnoDB table

CREATE TABLE big_table_new LIKE big_table;
ALTER TABLE big_table_new MODIFY COLUMN id INT(11);
INSERT INTO big_table_new SELECT * FROM big_table;
ALTER TABLE big_table RENAME big_table_bak;
ALTER TABLE big_table_new RENAME big_table;

When you are done, big_table_bak is your backup.

If you are satisfied with the new big_table you can run

DROP TABLE big_table_bak;

If you want to revert back you can run

DROP TABLE big_table;
ALTER TABLE big_table_bak RENAME big_table;

Give it a Try !!!

Upvotes: 2

matsko
matsko

Reputation: 22183

First, before anything, backup the table:

mysqldump -uroot -p db big_table > /tmp/big_table.backup.sql

One thing to keep in mind when doing an ALTER command is to absolutely make sure that you have the same column details for the alter that you would like to not change.

So for example:

ALTER TABLE big_table MODIFY COLUMN id INT(11)

Would skip anything else like AUTO_INCREMENT and NOT NULL. So best to include that also into the alter statement.

ALTER TABLE big_table MODIFY COLUMN id INT(11) NOT NULL AUTO_INCREMENT;

Basically just copy over the create statement area for the ID column and replace what you want and include that into the modify statement.

Upvotes: 5

Adam Bieńkowski
Adam Bieńkowski

Reputation: 585

What do You think about:

  1. export all data and truncate table
  2. alter table
  3. change name of row in exported file
  4. import file

It's just an fast idea, but it won't be fast (exporting/importing).

Or Just try to do ALTER during night few hour maintenance? :)

Upvotes: 0

Related Questions