是否有MySQL命令删除除PRIMARY索引之外的所有索引？ [英] Is there a MySQL command drop all indexes except PRIMARY index?
问题描述
我有一个数据库表，其中一个索引的键名为PRIMARY，Type为BTREE，Unique为YES，Packed为NO，Column为ID，Cardinality为728，Collation为A.
I have a database table with one Index where the keyname is PRIMARY, Type is BTREE, Unique is YES, Packed is NO, Column is ID, Cardinality is 728, and Collation is A.
我有一个在页面加载时运行的脚本，它将条目添加到MySQL数据库表中，并从数据库表中删除重复项。
I have a script that runs on page load that adds entries to the MySQL database table and also removes duplicates from the Database Table.
下面是删除重复项的脚本部分：
Below is the script section that deletes the duplicates:
// Removes Duplicates from the MySQL Database Table
// Removes Duplicates from the MySQL Database Table based on 'Entry_Date' field
mysql_query("Alter IGNORE table $TableName add unique key (Entry_Date)");
// Deletes the added index created by the Removes Duplicates function
mysql_query("ALTER TABLE $TableName DROP INDEX Entry_Date");
使用上面的Remove Duplicates命令，会向表中添加一个附加索引。下一行命令是假设删除此添加的索引。
Using the Remove Duplicates command above, an additional index is added to the table. The next line command is suppose to delete this added index.
问题是，删除重复项命令创建的添加索引有时不会被添加的以下删除删除index命令因此会向表中添加更多索引。这些附加索引阻止脚本向数据库添加其他数据，直到我手动删除添加的索引。
The problem is that sometimes the added index created by the Removes Duplicates command does not get deleted by the following Delete added index command and therefore more indexes are added to the table. These additional indexes prevent the script from adding additional data to the database until I remove the added indexes by hand.
我的问题：
是否有命令或短我可以添加到脚本中的函数将删除除本文开头提到的原始索引之外的所有索引吗？
My Question: Is there a command or short function that I can add to the script that will delete all indexes except the original index mentioned in the beginning of this post?
我确实阅读了以下帖子，但我不知道我不知道这是否是正确使用的脚本：
如何使用单个查询删除除主键之外的所有索引
I did read the following post, but I don't know if this is the correct script to use: How to drop all of the indexes except primary keys with single query
推荐答案
<在所有评论之后，我想我意识到发生了什么。
您实际上允许数据库中的重复项。你只想清理一下它们。
问题是你选择清理它们的方法是创建一个Unique键并使用IGNORE选项，这会导致重复行被删除而不是创建唯一键失败。然后删除唯一键，以便可以再次添加重复的行。你的问题是有时不会删除唯一键。
After all the comments I think I realize what is happening. You actually allow duplicates in the database. You just want to clean them some times. The problem is that the method you have chosen to clean them is through creating a Unique key and using the IGNORE option which causes duplicate lines to get dropped instead of failing the unique key creation. then you drop the unique key so that duplicate rows can be added again. your problem is that sometimes the unique key is not being dropped.
我建议你以另一种方式删除重复项。假设您的表名是my_table，主键是my_mey_column，那么：
I suggest you delete the duplicates in another way. supposing that your table name is "my_table" and your primary key is my_mey_column then:
delete from my_table where my_key_column not in (select min(my_key_column) from my_table group by Entry_Date)
编辑：以上由于@a_horse_with_no_name指向的mysql限制将无法工作
Edit: the above won't work due to limitation in mysql as pointed by @a_horse_with_no_name
请尝试以下三个查询：
create temporary table if not exists tmp_posting_data select id from posting_data where 1=2
insert into tmp_posting_data(id) select min(id) from posting_data group by Entry_Date
delete from Posting_Data where id not in (select id FROM tmp_posting_data)
As最后一点，尝试重新考虑是否需要允许行复制，如@a_horse_with_no_name所示。而不是允许输入然后删除行，您可以在数据库中创建一次唯一键，如：
As a final note, try to reconsider the need to allow the rows to be duplicated also as suggested by @a_horse_with_no_name. instead of allowing rows to be entered and then deleted, you can create the unique key once in the database like:
Alter table posting_data add unique key (Entry_Date)
然后，当您从RSS插入新数据时使用以下内容而不是插入使用替换将删除旧行，如果它是主键或任何唯一索引上的重复
and then, when you are inserting new data from the RSS use the following instead of "insert" use "replace" which will delete the old row if it is a duplicate on the primary key or any unique index
replace into posting_data (......) values(.....)
这篇关于是否有MySQL命令删除除PRIMARY索引之外的所有索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
