如何在MySQL中重命名索引 [英] How do I rename an Index in MySQL

问题描述

我想重命名一个索引。我查看了 alter table 文档，但是我无法弄清楚简单地重命名索引的语法。通过MySQL GUI执行此操作时，它会删除索引，并创建一个新索引。虽然这有效，但我想避免重建整个索引只是为了更改索引的名称。

I would like to rename an index. I've looked at the alter table documentation, 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.

[ADDITIONAL INFO]

[ADDITIONAL INFO]

在alter table文档中声明

In the alter table documentation it states

仅修改表
元数据而不是表数据的更改可以通过更改
表的.frm文件而不是触及
表内容立即获得
。以下更改
是快速更改，可以通过这种方式
：

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文件（在测试数据库上）并重新启动服务器，它现在在尝试列出列时在UI中指出无法获取列，并且在尝试运行查询时，它返回错误未知表引擎 。 .frm文件有很多二进制内容。是否有一个很好的工具来编辑二进制信息。

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.

推荐答案

我在2009年回答了这个问题。当时没有语法在MySQL中重命名索引。

I answered this question in 2009. At that time there was no syntax in MySQL to rename an index.

从那时起，MySQL 5.7引入了一个 ALTER TABLE RENAME INDEX 语法。

Since then, MySQL 5.7 introduced an ALTER TABLE RENAME INDEX syntax.

http://dev.mysql.com/doc/refman/5.7/en/alter-table.html 部分说：

• RENAME INDEX old_index_name TO new_index_name 重命名索引。这是标准SQL的MySQL扩展。表的内容保持不变。 old_index_name 必须是表中未被同一 ALTER TABLE 语句删除的现有索引的名称。 new_index_name 是新索引名称，在应用更改后，不能复制结果表中索引的名称。索引名称都不能是 PRIMARY 。

• RENAME INDEX old_index_name TO new_index_name renames an index. This is a MySQL extension to standard SQL. The content of the table remains unchanged. old_index_name must be the name of an existing index in the table that is not dropped by the same ALTER TABLE statement. new_index_name is 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 be PRIMARY.

MySQL的早期版本，例如： 5.6及更早版本，在 ALTER中不支持任何语法表 重命名索引（或键，它是同义词）。

Earlier versions of MySQL, e.g. 5.6 and earlier, support no syntax in ALTER TABLE to rename an index (or key, which is a synonym).

唯一的解决方案是 ALTER TABLE DROP KEY oldkeyname，ADD KEY newkeyname（...）。

MySQL中没有 ALTER INDEX 命令。您只能 DROP INDEX 然后 使用新名称创建INDEX 。

There is no ALTER INDEX command in MySQL. You can only DROP INDEX and then CREATE INDEX with the new name.

关于您的更新：或许文档不够精确。无论如何，没有SQL语法来重命名索引。

Regarding your update above: perhaps the documentation isn't precise enough. Regardless, there's no SQL syntax to rename an index.

索引是一种可以从数据重建的数据结构（事实上，建议使用<来定期重建索引） code> OPTIMIZE TABLE ）。这需要一些时间，但这是一种常见的操作。索引数据结构与表数据是分开的，因此添加或删除索引不需要触及表数据，如文档所述。

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.

关于 .frm 文件，MySQL不支持编辑 .frm 文件。我不会出于任何原因这样做。您100％保证会损坏您的桌子并使其无法使用。

Regarding the .frm file, MySQL does not support editing the .frm file. I wouldn't do it for any reason. You are 100% guaranteed to corrupt your table and make it unusable.

这篇关于如何在MySQL中重命名索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！