MySQL 截断表需要很长时间，这正常吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17762361/
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
truncate table taking very long time, is it normal?
提问by smrati katiyar
I am using truncate table table_name; on a table with around 1 million rows, but it's been taking too long, running since last 3 hours.
我正在使用截断表 table_name; 在一个大约有 100 万行的表上，但它已经花费了太长时间，从过去 3 小时开始运行。
Is it normal? Can you suggest some other way to delete all rows from a table, which could be faster?
正常吗？您能否建议一些其他方法来删除表中的所有行，这可能会更快？
回答by Kalaiarasan Manimaran
Truncate wont work in some cases such as ,
截断在某些情况下不起作用，例如，
when you have index kind of things and some foreign key constraints
当你有索引类型的东西和一些外键约束时
Easy way i suggest is
我建议的简单方法是
RENAME TABLE table_name TO t1;
CREATE TABLE table_name LIKE t1;
DROP TABLE t1;
or you can also use DELETE FROM table_name;
或者你也可以使用 DELETE FROM table_name;
回答by Uriil
I believe deadlock occurs during your query execution, so it's better to kill it .
我相信在您的查询执行期间会发生死锁，因此最好将其杀死。
I used to delete lots of data, by deleting small chunk in a single query (10k works fine).
我曾经通过在单个查询中删除小块来删除大量数据（10k 工作正常）。
So you might write some script which will do this for you.
因此，您可能会编写一些脚本来为您执行此操作。
回答by Balkrushna Patil
Given issue is logged in mysql repo. For more details visit https://bugs.mysql.com/bug.php?id=68184
给定的问题记录在 mysql 存储库中。有关更多详细信息，请访问https://bugs.mysql.com/bug.php?id=68184
In my case, I was trying to trucate table using JDBC But due the above i was not able to do that. So was going through below alternative.
就我而言，我试图使用 JDBC 截断表，但由于上述原因，我无法做到这一点。所以正在经历以下替代方案。
String createTmpTable = "create table tmp_" + tableName + " like " + tableName;
String dropTable = "drop table " + tableName;
String reCreateTable = "create table " + tableName + " like tmp_" + tableName;
String droptmpTable = "drop table tmp_" + tableName;
