22

I am attempting to clean out a table but not get rid of the actual structure of the table. I have an id column 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.

How do I remove all of the records from the table so that I can insert new data?

Air
  • 8,274
  • 2
  • 53
  • 88
Doug Molineux
  • 12,283
  • 25
  • 92
  • 144

6 Answers6

27

drop table will remove the entire table with data

delete * from table will remove the data, leaving the autoincrement values alone. it also takes a while if there's a lot of data in the table.

truncate table will 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.

chris
  • 9,745
  • 1
  • 27
  • 27
  • For InnoDB `TRUNCATE` isn't that fast, because it does DROP+CREATE internally. Truncating 200 small tables takes several seconds on my dev system... – Marki555 Mar 26 '13 at 14:39
  • 3
    deleting data is a fully logged operation. Truncating data is minimally logged – Chris Wood Nov 04 '16 at 10:27
15

TRUNCATE will 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:

ALTER TABLE t2 AUTO_INCREMENT = value
davek
  • 22,499
  • 9
  • 75
  • 95
  • Good point on noting the auto-increment seed! I forgot to mention that –  Apr 08 '10 at 16:19
6

   Drop will do just that....drop the table in question, unless the table is a parent to another table.

   Delete will remove all the data that meets the condition; if no condition is specified, it'll remove all the data in the table.
   Truncate is 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 delete removes the data by each row, thus having a performance hit than truncate. However, truncate will not work on InnoDB tables where referential integrity is enforced unless it is turned off before the truncate command is issued.
   So, relax; unless you issue a drop command on the table, it won't be dropped.

Kneel-Before-ZOD
  • 4,141
  • 1
  • 24
  • 26
2

Truncate table is what you are looking for http://www.1keydata.com/sql/sqltruncate.html

1

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 : 

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;
Yaroslav
  • 6,476
  • 10
  • 48
  • 89
Vincent B.
  • 11
  • 2
0

I've just come across a situation where DELETE is drastically affecting SELECT performance compared to TRUNCATE on a full-text InnoDB query.

If I DELETE all rows and then repopulate the table (1million rows), a typical query takes 1s to come back.

If instead I TRUNCATE the table, and repopulate it in exactly the same way, a typical query takes 0.05s to come back.

YMMV, but for whatever reason for me on MariaDB 10.3.15-MariaDB-log DELETE seems to be ruining my index.

Codemonkey
  • 4,455
  • 5
  • 44
  • 76