更改列名而无需重新创建MySQL表 [英] Change column name without recreating the MySQL table
问题描述
是否可以在不进行重大更改的情况下重命名InnoDB表上的列?
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.
推荐答案
不幸的是，重命名列(使用ALTER TABLE ... CHANGE COLUMN)要求MySQL运行完整的表副本.
Renaming a column (with ALTER TABLE ... CHANGE COLUMN) unfortunately requires MySQL to run a full table copy.
查看 pt-online-schema-change .这可以帮助您对表进行多种类型的ALTER更改，而无需在ALTER持续时间内锁定整个表.将数据复制到新表中时，您可以继续对其进行读写操作.捕获更改并将其通过触发器应用于新表.
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.
示例:
pt-online-schema-change h=localhost,D=databasename,t=tablename \
--alter 'CHANGE COLUMN oldname newname NUMERIC(9,2) NOT NULL'
更新: MySQL 5.6可以执行某些类型的ALTER操作而无需重建表，并且更改列名是在线更改中受支持的一项.参见 http://dev.mysql.com /doc/refman/5.6/en/innodb-create-index-overview.html 概述哪些更改类型支持或不支持此更改.
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.
这篇关于更改列名而无需重新创建MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
