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.
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 TABLEcommand 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.
TRUNCATE TABLEstatement resets the value in the
AUTO_INCREMENTcolumn to its start value if the table has an AUTO_INCREMENT column.
TRUNCATE TABLEstatement does not fire the
DELETEcommand 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 COLUMNstatement 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
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.
You can use the following statement to truncate the
TRUNCATE TABLE students;
Here is the output:
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
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
TRUNCATE TABLE mysqltutorial.products;
How to truncate all tables in MySQL?
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.
Now, we will be able to truncate tables. After truncating the tables, re-enable the foreign key checks using the following command.