命令删除表的所有非主索引

Is there a MySQL command that can drop all the extra indexes except for the first one in a single table thus keeping only the primary index?

是否有一个MySQL命令可以删除除单个表中的第一个索引之外的所有额外索引，从而只保留主索引?

I can drop the second Post_Date index using the following MySQL command, but I am having problems dropping all the rest of them.

我可以使用下面的MySQL命令删除第二个Post_Date索引，但是我无法删除其余的所有索引。

mysql_query("ALTER TABLE $TableName DROP INDEX Post_Date");

The extra Post_Date and Post_Date_x indexes are created in the beginning of the script, so I want to delete them at the end of the script using a MySQL command at the end of the script.

在脚本的开头创建了额外的Post_Date和Post_Date_x索引，因此我想在脚本末尾使用MySQL命令删除它们。

Keep in mind that _x in Post_Date_x and vary and could go from 1 to 10, or from 1 to 100. So a Loop or IF statement may be needed.

记住，Post_Date_x中的_x可以从1到10，也可以从1到100。所以可能需要一个循环或者IF语句。

The MySQL command will be part of a PHP script

MySQL命令将是PHP脚本的一部分

Thank you for your time.

谢谢您的时间。

Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
 Edit    Drop   PRIMARY BTREE   Yes No  id  830 A       
 Edit    Drop   Post_Date   BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_2 BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_3 BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_4 BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_5 BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_6 BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_7 BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_8 BTREE   Yes No  Post_Date   830 A

2 个解决方案

#1

This should do it -

这应该可以做到

$res = mysql_query("SHOW INDEX FROM `$TableName` WHERE `Key_name` LIKE 'Post_Date%'");

while ($row = mysql_fetch_object($res)) {
    mysql_query("DROP INDEX `{$row->Key_name}` ON `{$row->Table}`") or die(mysql_error());
}

#2

Sorry but there is no MySQL comand for ALTER TABLE ny_table DROP ALL INDEXES EXCEPT PRIMARY KEY.

抱歉，除了主键外，ALTER TABLE ny_table没有MySQL comand删除所有索引。

You would have to create a specific name for each index as it was created, and then create a loop to individually run an ALTER TABLE ... DROP INDEX SQL command for each index using the same names.

您必须为创建的每个索引创建一个特定的名称，然后创建一个循环以单独运行ALTER TABLE……使用相同的名称为每个索引删除索引SQL命令。

Note also that creating indexes is rather expensive - unless you are doing > ~200 queries that are optimized by the index, creating and deleting the index probably takes more time than is saved by using them.

还要注意，创建索引是相当昂贵的——除非您正在执行由索引优化的> ~200查询，否则创建和删除索引可能要比使用它们节省更多的时间。

#1