Delete, Truncate or Drop to clean out a table in M

问题:

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?

回答1:

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

回答2:

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.

回答3:

   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.

回答4:

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

回答5:

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;