Change mysql field name in a huge table

Question

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?

Answer 1

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.