MySQL TRUNCATE TABLE

Summary: in this tutorial, you will learn how to remove all the rows from a table using the MySQL TRUNCATE TABLE statement with the help of examples.

The MySQL TRUNCATE TABLE statement allows us to remove all the rows from a MySQL table. Generally, we use this command when we want to delete all the rows from the table without losing the table structure.

This command is similar to the DELETE TABLE command without specifying the WHERE clause. However, the TRUNCATE command is faster than the DELETE command as it does not maintain any transactional log. Internally, this command drops and recreates the table.

The following points must be remembered while working with the TRUNCATE TABLE statement in MySQL.

  • The operation cannot be rollback as the TRUNCATE TABLE command does not maintain a log and the commit is implicit.
  • The truncate statement fails if the table is referenced by a foreign key or participates in an indexed view.
  • The TRUNCATE TABLE statement resets the value in the AUTO_INCREMENT column to its start value if the table has an AUTO_INCREMENT column.
  • The TRUNCATE TABLE statement does not fire the DELETE command triggers associated with the truncated table as it does not operate on individual rows.
  • As this statement does operate on individual rows, drops, and recreates the table internally, the number of rows affected by the TRUNCATE COLUMN statement is zero (0), unlike the delete statement that returns the number of deleted rows.

MySQL TRUNCATE TABLE Syntax

The following is the basic syntax of the TRUNCATE TABLE command.

TRUNCATE [TABLE] table_name;

In this syntax, we specify the name of the table from that the complete data to be removed after the TRUNCATE TABLE keywords. Here, the TABLE keyword is optional. However, it is a good practice to use the TABLE keyword to distinguish between the TRUNCATE TABLE and the TRUNCATE() function.

MySQL TRUNCATE TABLE Example

Let us now understand the concept of the MySQL TRUNCATE TABLE with the help of some examples. Suppose, we have a table named students with the following data.

mysql truncate table

You can use the following statement to truncate the students table.

TRUNCATE TABLE students;

Here is the output:

mysql truncate table

As you can see from the above image that the command TRUNCATE TABLE was successfully executed and it shows 0 rows affected. After issuing the SELECT statement, it does not return any data, instead, it is showing an Empty set.

Let’s check another example of truncating a table by specifying the table name along with the database name.

The below example truncates a table name products from the mysqltutorial database.

TRUNCATE TABLE mysqltutorial.products;

Output:

mysql truncate table

How to truncate all tables in MySQL?

The TRUNCATE TABLE command deletes only one table at a time. If you want to delete more than one table at a time, you can use the following syntax.

TRUNCATE TABLE table_name1; 
TRUNCATE TABLE table_name2; 
......
TRUNCATE TABLE table_nameN;

To generate the TRUNCATE TABLE statement for all the tables that belongs to a specific database, you can use the following query.

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_schema = 'database_name';

How to Truncate Table with Foreign key?

If we try to truncate a table that uses a foreign key constraint, we will get the following error.

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

In this case, we need to disable the foreign key check before issuing the TRUNCATE TABLE command. You use the following statement for the same.

SET FOREIGN_KEY_CHECKS=0;

Now, we will be able to truncate tables. After truncating the tables, re-enable the foreign key checks using the following command.

SET FOREIGN_KEY_CHECKS=1;