MySQL TRUNCATE Table
The TRUNCATE statement in MySQL removes the complete data without removing its structure. It is a part of DDL or data definition language command. Generally, we use this command when we want to delete an entire data from a table without removing the table structure.
The TRUNCATE command works the same as a DELETE command without using a WHERE clause that deletes complete rows from a table. However, the TRUNCATE command is more efficient as compared to the DELETE command because it removes and recreates the table instead of deleting single records one at a time. Since this command internally drops the table and recreates it, the number of rows affected by the truncate statement is zero, unlike the delete statement that returns the number of deleted rows.
This command does not maintain the transaction log during the execution. It deallocates the data pages instead of rows and makes an entry for the deallocating pages instead of rows in transaction logs. This command also locks the pages instead of rows; thus, it requires fewer locks and resources.
The following points must be considered while using the TRUNCATE command:
- We cannot use the WHERE clause with this command so that filtering of records is not possible.
- We cannot rollback the deleted data after executing this command because the log is not maintained while performing this operation.
- We cannot use the truncate statement when a table is referenced by a foreign key or participates in an indexed view.
- The TRUNCATE command doesn’t fire DELETE triggers associated with the table that is being truncated because it does not operate on individual rows.
Syntax
The following syntax explains the TRUNCATE command to remove data from the table:
In this syntax, first, we will specify the table name which data we are going to remove. The TABLE keyword in the syntax is not mandatory. But it’s a good practice to use it to distinguish between the TRUNCATE() function and the TRUNCATE TABLE statement.
MySQL Truncate Table Example
Let us demonstrate how we can truncate the table with the help of an example. First, we are going to create a table named “customer” using the below statement:
Next, we will add values to this table using the below statement:
Now, verify the table by executing the SELECT statement whether the records inserted or not:
We will get the output, as shown below:
Now, execute the following statement that truncates the table customer using the TRUNCATE syntax discussed above:
After the successful execution, we will get the following output:
As we can see, this query returns 0 rows are affected even if all the table records are deleted. We can verify the deletion of the data by executing the SELECT statement again. This command gives the following output that shows none of the records present in the table:
How to Truncate Table with Foreign key?
If we perform the TRUNCATE operation for the table that uses a foreign key constraint, we will get the following error:
In that case, we need to log into the MySQL server and disable foreign key checks before executing the TRUNCATE statement as below:
Now, we are able to truncate tables. After execution, re-enable foreign key checks as given below:
How to truncate all tables in MySQL?
The TRUNCATE statement in MySQL will delete only one table at a time. If we want to delete more than one table, we need to execute the separate TRUNCATE statement. The below example shows how to truncate multiple tables in MySQL:
We can also use the below SQL query that generates several TRUNCATE TABLE commands at once using the table names in our database: