MySQL 删除表中的所有索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3798524/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL dropping all indexes from table
提问by aviv
I have a MySQL database that runs for some time now with many changes on it. Lately I looked over it and I noticed that in some cases I have doubled the index on the same field. Some Indexes are missing, and in general there is a huge mess in all the indexes.
我有一个运行了一段时间的 MySQL 数据库，上面有很多变化。最近我查看了它，我注意到在某些情况下，我将同一字段的索引翻了一番。一些索引丢失了，总的来说，所有索引都乱七八糟。
I wants to drop all indexes from a table. Later on I have a prepared script that will run ALTER TABLEand add the relevant indexes.
我想从表中删除所有索引。稍后我有一个准备好的脚本，它将运行ALTER TABLE并添加相关索引。
Is there a way to drop all indexes from a table?
有没有办法从表中删除所有索引？
采纳答案by Lo?c Février
If you have phpmyadmin or any similar tool you can do that very easily graphically.
如果你有 phpmyadmin 或任何类似的工具，你可以很容易地以图形方式完成。
Or for every index do something like
或者为每个索引做类似的事情
ALTER TABLE `table` DROP INDEX `NameIndex`
You can get the indexes with
你可以得到索引
SHOW INDEX FROM `table`
回答by Vojtech Kurka
Simple script:
简单的脚本：
-- list all non-unique indexes
SELECT table_name AS `Table`,
index_name AS `Index`,
GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE NON_UNIQUE = 1 AND table_schema = 'mydatabase' AND table_name = 'mytable'
GROUP BY 1,2;
-- drop all non-unique indexes
SET SESSION group_concat_max_len=10240;
SELECT CONCAT('ALTER TABLE ', `Table`, ' DROP INDEX ', GROUP_CONCAT(`Index` SEPARATOR ', DROP INDEX '),';' )
FROM (
SELECT table_name AS `Table`,
index_name AS `Index`
FROM information_schema.statistics
WHERE NON_UNIQUE = 1 AND table_schema = 'mydatabase' AND table_name = 'mytable'
GROUP BY `Table`, `Index`) AS tmp
GROUP BY `Table`;
-- add all non-unique indexes , WITHOUT index length spec
SET SESSION group_concat_max_len=10240;
SELECT CONCAT('ALTER TABLE ', `Table`, ' ADD INDEX ', GROUP_CONCAT(CONCAT(`Index`, '(', `Columns`, ')') SEPARATOR ',\n ADD INDEX ') )
FROM (
SELECT table_name AS `Table`,
index_name AS `Index`,
GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE NON_UNIQUE = 1 AND table_schema = 'mydatabase' AND table_name = 'mytable'
GROUP BY `Table`, `Index`) AS tmp
GROUP BY `Table`;
回答by Fernando Kosh
In Ruby on Rails I do this:
在 Ruby on Rails 中，我这样做：
indexes = ActiveRecord::Base.connection.execute("SHOW INDEX FROM tablename")
indexes.each do |index|
ActiveRecord::Base.connection.execute("ALTER TABLE tablename DROP INDEX #{index[2]};")
end
回答by The Surrican
no there isnt a command. you can however write a script that shows all databases, shows all tables inside thowe databases, shows all indexes inside those tables and drops them all. but i'n not gonna write that for you if you don't start accepting some answers. you can also use phpmyadmin or another graphical tool to select this neat "check all" box for every table.
不，没有命令。但是，您可以编写一个脚本来显示所有数据库、显示数据库中的所有表、显示这些表中的所有索引并将它们全部删除。但如果你不开始接受一些答案，我不会为你写的。您还可以使用 phpmyadmin 或其他图形工具为每个表选择这个整洁的“全选”框。
