在一个巨大的表中更改mysql字段名称

Question

我有一个拥有2100万行的表格。我需要更改其中一行的名称。当我使用查询语句"alter table company change id new_id int(11)"时，查询永远不会结束。

有没有简单的方法可以更改大型MySQL表格的字段名称？

- demircan

---

你可以使用mySqldump命令导出数据并编辑导出的文件，然后重新导入数据。 - ethrbunny

我相信你的建议是有道理的，但我只想更改字段名称。表的转储将近10 GB，这似乎不是一种高效的方式。 - demircan

如果您正在使用InnoDB，我想您只能等待它完成：“仅修改表元数据而不是表数据的更改可以通过修改表的.frm文件并且不触及表内容来立即进行。以下更改是可以通过这种方式快速进行的更改：重命名列，但不适用于InnoDB存储引擎。” http://dev.mysql.com/doc/refman/5.5/en/alter-table.html - Joni

文档称重命名列（除InnoDB存储引擎外）仅修改表元数据而不是表数据，可以通过更改表的.frm文件而立即完成，而无需触及表内容。但对于我来说情况并非如此。 - demircan

你能解决这个问题吗？ - subodh

Answer 1

首先，在做任何事情之前，请备份表：

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

在执行ALTER命令时，需要牢记的一点是，务必确保您希望不更改的列具有相同的列详细信息。

例如：

ALTER TABLE big_table MODIFY COLUMN id INT(11)

最好将 AUTO_INCREMENT 和 NOT NULL 等内容一同包含在修改语句中，以避免遗漏。

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

基本上只需复制ID列的创建语句区域，然后替换所需的内容并将其包含到修改语句中即可。

- matsko

---

如果修改出现问题，您可以在 big_table.backup.sql 备份文件中搜索和替换 CREATE TABLE 语句中的 id INT(11) 字段以匹配您想要的内容，然后使用 mysql -uroot -p db < /tmp/big_table.backup.sql 正常导入。 - matsko

谢谢。我正在尝试。我会发布结果。 - demircan

我已经尝试了这个建议。是的，它按预期工作。我使用了split和cat来编辑大型SQL文件。 - demircan

很好。或许可以加上您所做的更改和步骤，以使其完全工作，然后我们就可以结束这个问题了 :) - matsko

Answer 2

使用pt-online-schema-change进行长时间的ALTER TABLE更改，而不会将自己锁定在表中。

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

通常需要更长的时间才能完成更改，但在执行此操作期间仍然可以从表中读取和写入数据。

您还可以听取作者的网络研讨会，了解其工作原理及其注意事项：

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

- Bill Karwin

Answer 3

您可以简单地按照以下步骤操作：

MyISAM表

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;

完成后，big_table_bak 就是您的备份。

如果您对新的 big_table 感到满意，可以运行

DROP TABLE big_table_bak;

如果你想回滚，可以执行以下命令：

DROP TABLE big_table;
ALTER TABLE big_table_bak RENAME big_table;

尝试一下吧！

- RolandoMySQLDBA

---

如果你有2100万行数据，这并不是那么简单的事情。 - Jānis Elmeris

是的，我有7700万条记录。这可能需要太长时间了，我会晚些时候评论需要多长时间。 - zero8

Answer 4

在一个巨大的 MySQL 表中更改字段名称的较少耗时的方法是：

创建新的大表，选择 big_table 中的 id 作为新的 id，同时选择 field1、field2 和 field3。

- demircan

Answer 5

您对以下内容有何看法：

1. 导出所有数据并截断表
2. 修改表
3. 更改导出文件中的行名称
4. 导入文件

这只是一个快速想法，但导入/导出过程可能不够快。

或者尝试在夜间进行几小时的维护期间执行ALTER操作？ :)

- Adam Bieńkowski