MySQL Truncate Table statement removes all the records or complete data without effecting 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. The basic syntax of the MySQL Truncate statement is
TRUNCATE TABLE `Schema_Name`.`Table_Name`
We are going to use the following table to demonstrate this MySQL Truncate table.
MySQL Truncate Table Example
In this example, we are going to truncate the above-specified table.
TRUNCATE TABLE sqltest.empdetails;
Let us see the data in the above table
SELECT EmpID, `FirstName`, `LastName`,Education, Occupation, YearlyIncome, Sales, HireDate FROM sqltest.empdetails;
As you can see, it returned 0 records.
- Well, the Truncate table automatically reset the AUTO_INCREMENT counter to the starting point, which is not the possible case for Delete statement.
- The truncate table statement drops the existing table and recreates the same. Because of this, any Delete triggers won’t fire during truncate.
Truncate Table using Command Prompt
It is another example of a Truncate statement using the command prompt or Terminal.
TRUNCATE TABLE sqltest.employeedetails;
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
It opens a pop-up window
If you want, you can review the SQL query generated by this option by selecting the Review SQL
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, the below query returns all the tables in a database. It is instrumental in finding the list of tables available for you. Then perform the required operations.
SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'sqltest'
Next, you can use the 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 tables. You can simply copy and execute those codes.