Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

13.2.9. TRUNCATE Syntax

TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

For InnoDB tables, TRUNCATE TABLE is mapped to DELETE if there are foreign key constraints that reference the table; otherwise fast truncation (dropping and re-creating the table) is used. The AUTO_INCREMENT counter is reset by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.

For other storage engines, TRUNCATE TABLE differs from DELETE in the following ways in MySQL 5.1:

  • Truncate operations drop and re-create the table, which is much faster than deleting rows one by one.

  • Truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.

  • The number of deleted rows is not returned.

  • As long as the table format file tbl_name.frm is valid, the table can be re-created as an empty table with TRUNCATE TABLE, even if the data or index files have become corrupted.

  • The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

  • When used with partitioned tables, TRUNCATE TABLE preserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions (.par) file is unaffected.

TRUNCATE TABLE is an Oracle SQL extension adopted in MySQL.


 
 
  Published under the terms of the GNU General Public License Design by Interspire