如何在 MySQL 中重命名索引

我想重命名一个索引。我已经查看了 更换桌子文档，但是我不能弄清楚仅仅重命名索引的语法。当通过 MySQLGUI 执行此操作时，它会删除索引，并创建一个新的索引。虽然这是可行的，但是我希望避免仅仅为了更改索引的名称而重新构建整个索引。

[附加信息]

在 alter 表的文档中，它声明

只修改表的更改 元数据而不是表数据可以是 立即通过修改 表的。从文件和不接触 表内容 是可以做出的快速改变 这边:

* Renaming a column or index.

但是，当我试图通过编辑。从文件(在测试数据库上)重新启动服务器时，它现在在 UI 中声明“无法获取列”，当尝试列出列时，并且当尝试运行查询时，它返回错误“ Unknown table engine”。那个。From 文件有很多二进制内容。有没有一个好的工具来编辑二进制信息。

91314 次浏览
小开
最佳答案

I answered this question in 2009. At that time there was no syntax in MySQL to rename an 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 says in part:

  • 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.

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).

The only solution was to ALTER TABLE DROP KEY oldkeyname, ADD KEY newkeyname (...).

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

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

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.

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.

小开

For MySQL 5.7:

ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name

For MySQL older versions:

ALTER TABLE tbl_name DROP INDEX old_index_name, ADD INDEX new_index_name (...)

See http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

小开

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.


提交答案