删除、截断或删除以清除 MySQL 中的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2601576/
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
Delete, Truncate or Drop to clean out a table in MySQL
提问by Doug Molineux
I am attempting to clean out a table but not get rid of the actual structure of the table. I have an idcolumn that is auto-incrementing; I don't need to keep the ID number, but I do need it to keep its auto-incrementing characteristic. I've found delete and truncate but I'm worried one of these will completely drop the entire table rendering future insert commands useless.
我试图清理一张桌子，但没有摆脱桌子的实际结构。我有一个id自动递增的列；我不需要保留 ID 号，但我确实需要它来保持其自动递增特性。我发现 delete 和 truncate 但我担心其中一个会完全删除整个表，从而使未来的插入命令无用。
How do I remove all of the records from the table so that I can insert new data?
如何从表中删除所有记录以便插入新数据？
采纳答案by davek
TRUNCATEwill reset your auto-increment seed (on InnoDB tables, at least), although you could note its value before truncating and re-set accordingly afterwards using alter table:
TRUNCATE将重置您的自动增量种子（至少在 InnoDB 表上），尽管您可以在截断之前记下它的值，然后使用alter table相应地重新设置：
ALTER TABLE t2 AUTO_INCREMENT = value
回答by chris
drop tablewill remove the entire table with data
drop table将删除包含数据的整个表
delete * from tablewill remove the data, leaving the autoincrement values alone. it also takes a while if there's a lot of data in the table.
delete * from table将删除数据，单独保留自动增量值。如果表中有很多数据，也需要一段时间。
truncate tablewill remove the data, reset the autoincrement values (but leave them as autoincrement columns, so it'll just start at 1 and go up from there again), and is very quick.
truncate table将删除数据，重置自动增量值（但将它们保留为自动增量列，因此它只会从 1 开始并从那里再次上升），并且非常快。
回答by Kneel-Before-ZOD
Dropwill do just that....drop the table in question, unless the table is a parent to another table.
Deletewill remove all the data that meets the condition; if no condition is specified, it'll remove all the data in the table.
Truncateis similar to delete; however, it resets the auto_increment counter back to 1 (or the initial starting value). However, it's better to use truncate over delete because deleteremoves the data by each row, thus having a performance hit than truncate. However, truncatewill not work on InnoDBtables where referential integrity is enforced unless it is turned off before the truncatecommand is issued.
So, relax; unless you issue a dropcommand on the table, it won't be dropped.
Drop会这样做....删除有问题的表，除非该表是另一个表的父表。
删除将删除所有符合条件的数据；如果没有指定条件，它将删除表中的所有数据。
截断类似于删除；但是，它会将 auto_increment 计数器重置回 1（或初始起始值）。但是，最好使用 truncate 而不是 delete ，因为delete会删除每一行的数据，因此比truncate 会降低性能。但是，除非在truncate之前将其关闭，否则truncate将不适用于强制执行参照完整性的InnoDB表命令发出。
所以，放松；除非您在桌子上发出drop命令，否则它不会被删除。
回答by Kneel-Before-ZOD
Truncate table is what you are looking for http://www.1keydata.com/sql/sqltruncate.html
截断表是您正在寻找的 http://www.1keydata.com/sql/sqltruncate.html
回答by Vincent B.
Another possibility involves creating an empty copy of the table, setting the AUTO_INCREMENT (with some eventual leeway for insertions during the non-atomic operation) and then rotating both :
另一种可能性涉及创建表的空副本，设置 AUTO_INCREMENT（在非原子操作期间有一些最终的插入余地），然后旋转两者：
CREATE TABLE t2_new LIKE t2;
SELECT @newautoinc:=auto_increment /*+[leeway]*/
FROM information_schema.tables
WHERE table_name='t2';
SET @query = CONCAT("ALTER TABLE t2_new AUTO_INCREMENT = ", @newautoinc);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
RENAME TABLE t2 TO t2_old, t2_new TO t2;
And then, you have the extra advantage of being still able to change your mind before removing the old table.
然后，您还有一个额外的优势，那就是在移除旧表之前仍然可以改变主意。
If you reconsider your decision, you can still bring back old records from the table before the operation:
如果您重新考虑您的决定，您仍然可以在操作前从表中带回旧记录：
INSERT /*IGNORE*/ INTO t2 SELECT * FROM t2_old /*WHERE [condition]*/;
When you're good you can drop the old table:
当你很好时，你可以删除旧表：
DROP TABLE t2_old;
回答by Codemonkey
I've just come across a situation where DELETE is drastically affecting SELECT performance compared to TRUNCATE on a full-text InnoDB query.
我刚刚遇到一种情况，与全文 InnoDB 查询上的 TRUNCATE 相比，DELETE 显着影响了 SELECT 性能。
If I DELETE all rows and then repopulate the table (1million rows), a typical query takes 1s to come back.
如果我删除所有行然后重新填充表（100 万行），典型的查询需要 1 秒才能返回。
If instead I TRUNCATE the table, and repopulate it in exactly the same way, a typical query takes 0.05s to come back.
相反，如果我截断表，并以完全相同的方式重新填充它，典型的查询需要 0.05 秒才能返回。
YMMV, but for whatever reason for me on MariaDB 10.3.15-MariaDB-log DELETE seems to be ruining my index.
YMMV，但无论出于何种原因，MariaDB 10.3.15-MariaDB-log DELETE 似乎都在破坏我的索引。
