Tutorial Gateway

MySQL Truncate Table

by

MySQL Truncate Table statement removes all the records or complete data without effecting the table structure or indexes. When you to remove all the existing records then MySQL Truncate Table statement is the best option for you.

MySQL Truncate Table Syntax

The basic syntax of the MySQL Truncate statement is 

TRUNCATE TABLE `Schema_Name`.`Table_Name`

We are going to use the below show table to demonstrate this Truncate table

MySQL Truncate Table Statement 1

MySQL Truncate Table Example

In this example, we are going to truncate the above specified table.

TRUNCATE TABLE sqltest.empdetails;

OUTPUT

MySQL Truncate Table Statement 2

Let us see the data in the above table

SELECT EmpID, `FirstName`, `LastName`,Education, 
       Occupation, YearlyIncome, Sales, HireDate
FROM sqltest.empdetails;

OUTPUT

MySQL Truncate Table Statement 3

As you can see, it was returned 0 records.

By seeing the above results, you might wonder, why we need Truncate when the Delete Statement without Where Clause can do the same?. 

  • Well, Truncate table will automatically reset the AUTO_INCREMENT counter to the starting point. Which is not the case for Delete statement.
  • The truncate table statement drops the existing table and recreate the same. Because of this, any Delete triggers will not fire during truncate.

Truncate Table using Command Prompt

This is an another example of Truncate statement using the command prompt or Terminal.

TRUNCATE TABLE sqltest.employeedetails;

OUTPUT

MySQL Truncate Table Statement 4

Truncate Table using Workbench

If you can access the MySQL Workbench then it was easy to truncate tables. First, select and right-click on the table that you want to truncate. Next, select the Truncate Table option

MySQL Truncate Table Statement 5

This will open a pop up window

MySQL Truncate Table Statement 6

If you want, you can review the SQL query generated by this option by selecting the Review SQL

MySQL Truncate Table Statement 7

Or else, simply click the Truncate button

MySQL Truncate all Tables

In MySQL, you can use the information_schema to get all the tables in a Database. From there, you can truncate the tables.

For instance, below code will return all the tables in a database. This is very useful to find the list of tables that a are available for you, and then perform the required operations.

SELECT TABLE_NAME
FROM information_schema.tables
WHERE table_schema = 'sqltest'

OUTPUT

MySQL Truncate All Tables 8

Next, you can use Truncate table statement to truncate one after the other.

TRUNCATE TABLE department

Or, You can use the below code to generate a statement to truncate all tables in MySQL

SELECT CONCAT('TRUNCATe TABLE ' , TABLE_NAME )
FROM information_schema.tables
WHERE table_schema = 'sqltest'

The above code returns the truncate table statements for all the table. You can simply copy and execute those codes.