My SQL 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:

TRUNCATE [TABLE] table_name;

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:

CREATE TABLE customer (    

    Id int PRIMARY KEY NOT NULL,     

    Name varchar(45) NOT NULL,     

    Product varchar(45) DEFAULT NULL,     

    Country varchar(25) DEFAULT NULL,     

    Year int NOT NULL    

);

Next, we will add values to this table using the below statement:

INSERT INTO customer ( Id, Name, Product, Country, Year)     

VALUES (1, 'Stephen', 'Computer', 'USA', 2015),     

(2, 'Joseph', 'Laptop', 'India', 2016),     

(3, 'John', 'TV', 'USA', 2016),    

(4, 'Donald', 'Laptop', 'England', 2015),    

(5, 'Joseph', 'Mobile', 'India', 2015),    

(6, 'Peter', 'Mouse', 'England', 2016);

Now, verify the table by executing the SELECT statement whether the records inserted or not:

mysql> SELECT * FROM customer;

We will get the output, as shown below:

Now, execute the following statement that truncates the table customer using the TRUNCATE syntax discussed above:

mysql> TRUNCATE TABLE customer;

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:

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

In that case, we need to log into the MySQL server and disable foreign key checks before executing the TRUNCATE statement as below:

SET FOREIGN_KEY_CHECKS=0;

Now, we are able to truncate tables. After execution, re-enable foreign key checks as given below:

SET FOREIGN_KEY_CHECKS=1;

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:

TRUNCATE TABLE table_name1;  

TRUNCATE TABLE table_name2;  

TRUNCATE TABLE table_name3;

We can also use the below SQL query that generates several TRUNCATE TABLE commands at once using the table names in our database:

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME)  

FROM INFORMATION_SCHEMA.TABLES  

WHERE table_schema = 'database_name';