MySQL Truncate Table Statement 4

MySQL Truncate Table

MySQL Truncate Table statement removes all the records or complete data without affecting the table structure or indexes. When you want to remove all the existing records, then the MySQL Truncate Table statement is the best option for you and the basic syntax of it is 

TRUNCATE TABLE `Schema_Name`.`Table_Name`

We are going to use the following to demonstrate this MySQL Truncate table.

Employee Records 1

MySQL Truncate Table Example

In this MySQL Truncate Table example, we are going to clear the above-specified.

TRUNCATE TABLE sqltest.empdetails;
MySQL Truncate Table Statement 2

Let us see the data in the above

SELECT EmpID, `FirstName`, `LastName`,Education, 
       Occupation, YearlyIncome, Sales, HireDate
FROM empdetails;
MySQL Truncate Table Statement 3

As you can see, it returned 0 records.

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

  • Well, it automatically reset the AUTO_INCREMENT counter to the starting point, which is not the possible case for Delete statement.
  • This statement drops the existing table and recreates the same. Because of this, any Delete triggers won’t fire during this process.

It is another example of it using the command prompt or Terminal.

TRUNCATE TABLE sqltest.employeedetails;
MySQL Truncate Table Statement 4

Using Workbench

If you can access the MySQL Workbench then it was easy to truncate tables. First, select and right-click on the one that you want to clear. Next, select the below-shown option

MySQL Truncate Table Statement 5

It opens a pop-up window

MySQL Truncate Table Statement 6

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

Review SQL Code to Execute 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 remove the required ones.

For instance, the below query returns all the available ones in a database. It is instrumental in finding the list of tables available for you. Then perform the required operations on them.

SELECT TABLE_NAME
FROM information_schema.tables
WHERE table_schema = 'sqltest'
List Of Tables 8

Next, you can use the this statement to clear one after the other.

TRUNCATE TABLE department

Or, You can use the below code to generate a statement to delete all of them in the database.

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

The above code returns the truncate statement for all of them. You can simply copy and execute those codes.