如何重命名 MySQL 中的索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1463363/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I rename an Index in MySQL
提问by Kibbee
I would like to rename an index. I've looked at the alter tabledocumentation, but I can't figure out the syntax to simply rename an index. When doing it through the MySQL GUI, it drops the index, and creates a new one. While this works, I would like to avoid rebuilding the entire index just to change the name of an index.
我想重命名一个索引。我查看了alter table文档，但我无法弄清楚简单地重命名索引的语法。通过 MySQL GUI 执行此操作时，它会删除索引并创建一个新索引。虽然这有效，但我想避免重建整个索引只是为了更改索引的名称。
[ADDITIONAL INFO]
[附加信息]
In the alter table documentation it states
在更改表文档中，它指出
Alterations that modify only table metadata and not table data can be made immediately by altering the table's .frm file and not touching table contents. The following changes are fast alterations that can be made this way:
* Renaming a column or index.
通过更改表的 .frm 文件而不涉及表内容，可以立即进行仅修改表元数据而不修改表数据的更改。以下更改是可以通过这种方式进行的快速更改：
* Renaming a column or index.
However, when I tried to rename the index by editing the .frm file (on a test database) and restarting the server, it now states "Could not fetch columns" in the UI when trying to list the columns, and when trying to run a query, it returns the error "Unknown table engine ''". The .frm file has a lot of binary content. Is there a good tool for editing the binary info.
但是，当我尝试通过编辑 .frm 文件（在测试数据库上）并重新启动服务器来重命名索引时，它现在在尝试列出列和尝试运行时在 UI 中指出“无法获取列”一个查询，它返回错误“未知表引擎''”。.frm 文件有很多二进制内容。是否有用于编辑二进制信息的好工具。
回答by Bill Karwin
I answered this question in 2009. At that time there was no syntax in MySQL to rename an index.
我在 2009 年回答过这个问题。当时 MySQL 中没有重命名索引的语法。
Since then, MySQL 5.7 introduced an ALTER TABLE RENAME INDEXsyntax.
从那时起，MySQL 5.7 引入了一种ALTER TABLE RENAME INDEX语法。
http://dev.mysql.com/doc/refman/5.7/en/alter-table.htmlsays in part:
http://dev.mysql.com/doc/refman/5.7/en/alter-table.html部分说：
RENAME INDEX old_index_name TO new_index_namerenames an index. This is a MySQL extension to standard SQL. The content of the table remains unchanged.old_index_namemust be the name of an existing index in the table that is not dropped by the sameALTER TABLEstatement.new_index_nameis the new index name, which cannot duplicate the name of an index in the resulting table after changes have been applied. Neither index name can bePRIMARY.
RENAME INDEX old_index_name TO new_index_name重命名索引。这是标准 SQL 的 MySQL 扩展。表的内容保持不变。old_index_name必须是表中未被同一ALTER TABLE语句删除的现有索引的名称。new_index_name是新的索引名称，在应用更改后不能与结果表中的索引名称重复。索引名称都不能是PRIMARY.
Earlier versions of MySQL, e.g. 5.6 and earlier, support no syntax in ALTER TABLEto rename an index (or key, which is a synonym).
MySQL 的早期版本，例如 5.6 和更早版本，不支持ALTER TABLE重命名索引（或键，这是同义词）的语法。
The only solution was to ALTER TABLE DROP KEY oldkeyname, ADD KEY newkeyname (...).
唯一的解决办法是ALTER TABLE DROP KEY oldkeyname, ADD KEY newkeyname (...)。
There is no ALTER INDEXcommand in MySQL. You can only DROP INDEXand then CREATE INDEXwith the new name.
ALTER INDEXMySQL 中没有命令。你只能DROP INDEX再用CREATE INDEX新名字。
Regarding your update above: perhaps the documentation isn't precise enough. Regardless, there's no SQL syntax to rename an index.
关于您上面的更新：也许文档不够精确。无论如何，没有重命名索引的 SQL 语法。
An index is a data structure that can be rebuilt from the data (in fact it's recommended to rebuild indexes periodically with OPTIMIZE TABLE). It takes some time, but it's a commonplace operation. Indexes data structures are separate from table data, so adding or dropping an index shouldn't need to touch the table data, as the documentation says.
索引是一种可以从数据重建的数据结构（实际上建议定期重建索引OPTIMIZE TABLE）。这需要一些时间，但这是一个司空见惯的操作。索引数据结构与表数据是分开的，因此添加或删除索引不需要触及表数据，如文档所述。
Regarding the .frmfile, MySQL does not support editing the .frmfile. I wouldn't do it for any reason. You are 100% guaranteed to corrupt your table and make it unusable.
关于.frm文件，MySQL 不支持编辑.frm文件。我不会出于任何原因这样做。您 100% 保证会损坏您的表并使其无法使用。
回答by joelparkerhenderson
For MySQL 5.7:
对于 MySQL 5.7：
ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name
For MySQL older versions:
对于 MySQL 旧版本：
ALTER TABLE tbl_name DROP INDEX old_index_name, ADD INDEX new_index_name (...)
回答by Ifedi Okonkwo
This question was asked ages ago, and was last updated over half a year ago. Still I feel the need to add this tip:
这个问题很久以前就被问到了，最后一次更新是在半年前。我仍然觉得有必要添加这个提示：
If the indexed column is used elsewhere as a foreign key, you may encounter an error related to that. Doing this may help:
如果索引列在其他地方用作外键，您可能会遇到与此相关的错误。这样做可能有帮助：
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE tbl DROP INDEX index_name;
ALTER TABLE tbl ADD INDEX new_index_name (indexed_column);
SET FOREIGN_KEY_CHECKS = 1;
Hope someone finds this useful.
希望有人觉得这很有用。
回答by another
For Oracle 11g(at least) it is like this:
对于Oracle 11g（至少），它是这样的：
ALTER INDEX upper_ix RENAME TO upper_name_ix;
