我试图清空一张表格,但不想丢掉表格的实际结构。我有一个自动增加的"id"列;我不需要保留ID号码,但是我需要保持其自动增加的特性。我已经找到了delete和truncate命令,但我担心这些命令会完全删除整个表格,从而使未来的插入命令无法使用。
如何删除表格中的所有记录,以便可以插入新数据?
如何删除表格中的所有记录,以便可以插入新数据?
drop table
会删除整个带有数据的表格。
delete * from table
会删除数据,但不会影响自增值。当表格中存在大量数据时,这条语句执行时间较长。
truncate table
会删除数据,并重置自增值(但保留自增列,所以其会从1开始重新递增),同时非常快速。
TRUNCATE
会重置您的自动递增种子(至少在InnoDB表上),尽管您可以在截断之前记录其值,并使用alter table在之后重新设置:
ALTER TABLE t2 AUTO_INCREMENT = value
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;
我刚遇到这样一种情况,DELETE 操作对于全文 InnoDB 查询的 SELECT 性能影响非常大,相比之下 TRUNCATE 的影响要小得多。
如果我删除所有行并重新填充表格(100万行),一个典型的查询需要1秒才能返回结果。
如果我使用 TRUNCATE 操作表格,并以完全相同的方式重新填充它,一个典型的查询只需要0.05秒就能返回结果。
你的情况可能不同,但在我的 MariaDB 10.3.15-MariaDB-log 上,由于某种原因,DELETE 操作似乎破坏了我的索引。