Delete, Truncate or Drop MySQL question

Question

Hi Everyone!

I am attempting to clean out a table but not get rid of the actual structure of the table, i have the following columns: id, username, date, text

the id 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.

Thank you, Everybody!

Answer 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

Answer 2

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

Answer 3

truncate, doesnt ruin the table, merely acts as a reset :)

Answer 4

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.