truncate table in MySQL

TRUNCATE TABLE empties a MySQL database table completely, deletes all the rows in the table. Logically speaking this is equivalent to a DELETE statement that deletes all rows, but there are few differences under some circumstances. In InnoDB, TRUNCATE TABLE is mapped to DELETE, so there is no difference.

  • Truncate table operations drops and re-creates the table, which is much faster than deleting rows one by one.
  • Truncate operations are not transaction-safe, you will encounter and error if table is locked.
  • Number of deleted rows are not returned.
  • If Auto_Increment field is used, it starts from the beginning after Truncate statement.

The TRUNCATE statement is faster then DELETE statement when you want to empty the table but at the same time you want to preserve the table structure.

TRUNCATE [TABLE] TableName ;
 


SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

Right now I am in process of redesigning this website in ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more help to database professionals and make it easy for me to update contents on regular basis.