In MySQL SQL, the TRUNCATE TABLE statement is used to remove all the records from a table. It is bascially same as that of using the DELETE statement.

But there is one difference between DELETE and TRUNCATE. Depends on what database you are using, if you use the DELETE statment, the database server will normally generate the redo transaction log. This means that you can rollback the data after the delete action. However, if you use the TRUNCATE statement to remove the table data, the database server will not generate redo tranaction log. So the data cannot be recovered.

In this tutorial, you will learn how to use the "TRUNCATE TABLE" statement In MySQL SQL language. We will walkthrough the "TRUNCATE TABLE" syntax with detailed explanations and examples.

Syntax

The syntax of a the TRUNCATE TABLE statement is as follows:

---

Parameters Explanations

table

---

Examples - How to use the "TRUNCATE TABLE" statment with a simple condition

In this tutorial, we will use the well-known Northwind sample database for demonstration purpose. Below is a portion of data selected from the employee table:

id	last_name	first_name	job_title
1	Freehafer	Nancy	Sales Representative
2	Cencini	Andrew	Vice President, Sales
3	Kotas	Jan	Sales Representative
4	Sergienko	Mariya	Sales Representative
5	Thorpe	Steven	Sales Manager
6	Neipper	Michael	Sales Representative
7	Zare	Robert	Sales Representative
8	Giussani	Laura	Sales Coordinator
9	Hellung-Larsen	Anne	Sales Representative

Here is the example of truncate table records from the employee table:



After the truncate, the result will be like that:

id	last_name	first_name	job_title

In conclusion, since using the TRUNCATE TABLE command, the database server will not generate redo transaction log. Therefore, running TRUNCATE TABLE command is usually much more faster than using the DELETE statement. If you are sure you don't need any backup of the data, you should use te TRUNCATE TABLE command.