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 ;
 



 3/18/2008


Add Comments
Name: *
Email: *
URL:
Comments: *
 


Advertisements

MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.

SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.

SQL Server DBA

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

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