How to Truncate a Table in MySQL

At this point, you may need to clean a table and all the data stored in it while preserving the table structure. In such a scenario, MySQL truncate clause is a very effective query.

This tutorial highlights how to use MySQL TRUNCATE statements to remove all the data in a database table.

MySQL TRUNCATE statement is part of the Data Definition Language statements. However, its functions are similar to the DELETE statement, making it seem a part of Data Manipulation Language.

To use the TRUNCATE statement, you must have the DROP privileges on the database.

Features of Truncate

The following are some of the salient features of the TRUNCATE statement that makes it different from the DELETE statement:

1. A truncate operation cannot be rolled back as it performs an implicit commit.
2. It works by deleting the table and re-creating it, preserving its structure but not the data.
3. Truncate supports corrupt tables, removing all the data and restoring an empty table.
4. It does not invoke any delete triggers.
5. It preserves a table’s partitioning
6. The TRUNCATE statement does not return any information regarding the affected rows—meaning that the return value is 0.

Basic Usage

The general syntax for using the TRUNCATE statement is:

NOTE: You can skip the TABLE keyword, and the TRUNCATE statement will perform similarly. However, it is better to add the TABLE keyword to avoid confusion with the Truncate function.

Example Use Case

Let’s look at an example of how to use the TRUNCATE statement.

For this example, I will use the employees’ table provided in the resource below:

https://dev.mysql.com/doc/index-other.html

First, select a few values from the table to confirm it is not empty:

The output is shown below:

<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/05/echo/word-image-508.png" data-lazy- height="239" src="data:image/svg xml,” width=”880″>

Now that we have confirmed that the table is full of data, let us try to truncate the table as:

We first set the FOREIGN_KEY_CHECK variable to False because the TRUNCATE statement fails if the table contains constraints from other tables.

Once we have removed the ability to check constraints from other tables, we call the TRUNCATE statement to remove the data.

You can confirm by clicking select:

<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/05/echo/word-image-509.png" data-lazy- height="263" src="data:image/svg xml,” width=”880″>

CAUTION: Do not remove the check for constraints in tables in a real-world database.

Conclusion

This guide walked you through how to use the TRUNCATE statement in MySQL to remove data in a table. I hope the tutorial was helpful.