Search code examples
mysqlsql-deletesql-drop

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

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?

Solution

  • 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