如何重命名 MySQL 中的索引

2021-12-21 00:00:00 indexing sql 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.

[附加信息]

在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 TABLE 重命名索引(或键，它是同义词).

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 然后 CREATE 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.

索引是一种可以从数据重建的数据结构(实际上建议使用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.

相关文章