删除、截断或删除以清理 MySQL 中的表

Question

我正在尝试清理表格，但没有摆脱表格的实际结构。我有一个自动递增的 id 列；我不需要保留ID号，但我确实需要它来保持其自动递增的特性。我发现了删除和截断，但我担心其中之一会完全删除整个表，从而使未来的插入命令变得无用。

如何从表中删除所有记录以便插入新数据？

Answer 1

drop table 将删除包含数据的整个表

delete * from table 将删除数据，仅保留自动增量值。如果表中有很多数据，也需要一段时间。

truncate table 将删除数据，重置自动增量值（但将它们保留为自动增量列，因此它会从 1 开始并再次从那里向上），并且速度非常快。

Answer 2

TRUNCATE 将重置你的自动增量种子（至少在 InnoDB 表上），尽管你可以在截断之前记下它的值，并在之后使用 更改表：

ALTER TABLE t2 AUTO_INCREMENT = value

Answer 3

   Drop 就会这样做....删除有问题的表，除非该表是另一个表的父表。

   删除将删除所有符合条件的数据；如果没有指定条件，它将删除表中的所有数据。
   截断与删除类似；但是，它将 auto_increment 计数器重置回 1（或初始值）。但是，最好使用截断而不是删除，因为删除会按行删除数据，因此比截断性能更高。但是，truncate 不适用于强制执行引用完整性的 InnoDB 表，除非在发出 truncate 命令之前将其关闭。
   所以，放松；除非您在桌子上发出 drop 命令，否则它不会被删除。

Answer 4

截断表就是您正在寻找的
http://www.1keydata.com/sql/sqltruncate.html

Answer 5

另一种可能性涉及创建表的空副本，设置 AUTO_INCRMENT （在非原子操作期间为插入提供一些最终余地），然后旋转两者：

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;

然后，您还有一个额外的优势，即仍然可以在之前改变主意删除旧表。

如果您重新考虑您的决定，您仍然可以在操作之前从表中取回旧记录：

INSERT /*IGNORE*/ INTO t2 SELECT * FROM t2_old /*WHERE [condition]*/;

当您准备好时，您可以删除旧表：

DROP TABLE t2_old;

Answer 6

我有点晚了，但是在 比较 SQL 中的 TRUNCATE TABLE、DELETE 和 DROP TABLE

Answer 7

我刚刚遇到过这样一种情况：与全文 InnoDB 查询上的 TRUNCATE 相比，DELETE 极大地影响了 SELECT 性能。

如果我删除所有行，然后重新填充表（100 万行），则典型的查询需要 1 秒才能返回。

如果我截断表，并以完全相同的方式重新填充它，则典型的查询需要 0.05 秒才能返回。

YMMV，但无论出于什么原因，我在 MariaDB 10.3.15-MariaDB-log DELETE 上似乎正在破坏我的索引。