MySQL TRUNCATE TABLE Statement

The TRUNCATE TABLE statement in MySQL removes all rows (records) from a table while preserving the table structure, including its columns, indexes, and constraints.

Unlike the DELETE statement, which removes rows one at a time, TRUNCATE TABLE deletes all rows in a single operation. As a result, it is significantly faster and more efficient for emptying large tables.

After executing the TRUNCATE TABLE statement:

• The table continues to exist.
• All rows are permanently removed.
• The table structure remains unchanged.
• All columns remain intact.
• Indexes are preserved.
• Constraints remain intact.
• Table permissions remain unchanged.
• The AUTO_INCREMENT counter is reset to its initial value.
• The table becomes empty and is ready to store new records.

You should use the TRUNCATE TABLE statement when you want to remove all data from a table quickly while keeping the table itself for future use. Think of TRUNCATE TABLE as emptying a container without throwing away the container.

Why Do We Need the MySQL TRUNCATE TABLE Statement?

In many situations, you may need to remove all rows from a table while keeping its structure intact. You do not want to remove all records individually when the table contains a large amount of data. For example, you may use the TRUNCATE TABLE statement to:

• Remove sample or testing data after development.
• Reload fresh data from another source.
• Empty temporary tables before reusing them.
• Reset a table during application testing.
• Clear log tables that are no longer needed.
• Remove imported data before importing a new dataset.

If you use the MySQL DELETE statement without a WHERE clause, these tasks can take more time because MySQL removes each row individually. In contrast, the TRUNCATE TABLE statement removes all rows in a single operation, making it significantly faster and more efficient for emptying an entire table.

When Should You Use the TRUNCATE TABLE Statement?

You should use the MySQL TRUNCATE TABLE statement when:

• You want to remove all rows from a table.
• You no longer need the existing data stored in the table.
• You want to empty a large table quickly.
• You want to improve performance compared to deleting rows individually.
• You want to reset the AUTO_INCREMENT counter to its initial value.
• You plan to insert a completely new set of records into the table.
• You want to reuse the existing table without dropping and recreating it.

Syntax of MySQL TRUNCATE TABLE

The syntax of the TRUNCATE TABLE statement in MySQL is:

TRUNCATE TABLE table_name;

In this syntax:

• TRUNCATE TABLE specifies the command to remove all rows from the specified table.
• table_name represents the name of the table you want to empty.

Example of MySQL TRUNCATE TABLE Statement

Let’s create a table in the MySQL database that we will use throughout this tutorial.

CREATE TABLE students ( 
    student_id INT AUTO_INCREMENT PRIMARY KEY, 
    student_name VARCHAR(50), 
    course VARCHAR(40), 
    city VARCHAR(30) 
);

Now we will insert some records in the table using the INSERT INTO statement in MySQL.

INSERT INTO students(student_name, course, city)
VALUES
('Rahul', 'Java', 'Delhi'),
('Tripti', 'Python', 'Godda'),
('Deepak', 'MySQL', 'Dhanbad'),
('Amit', 'PHP', 'Bengaluru');

The table now contains the following data.

Example 1: Remove All Rows from a Table

To remove all rows from a table, execute the following statement.

TRUNCATE TABLE students;

Output:

The table becomes empty.

What Happens Internally?

When you execute the TRUNCATE TABLE statement, MySQL:

• Removes all data from the table.
• Releases the storage occupied by the rows.
• Keeps the table definition.
• Preserves indexes and constraints.
• Resets the AUTO_INCREMENT counter.
• Makes the table empty and ready for new records.

Example 2: Verify That the Table Still Exists

Run the following query after truncating the table.

SELECT * FROM students;

Output:

Empty set

Notice that MySQL does not report an error such as “Table does not exist.”

Example 3: Insert New Records After TRUNCATE TABLE

Now we will insert a new record in the MySQL table.

INSERT INTO students(student_name, course, city)
VALUES ('Karan', 'Data Science', 'Pune');

After inserting a new record in the table, we will retrieve the data using the following SQL query.

SELECT * FROM students;

Output:

In this example, you observe that the new row receives student_id = 1. This happens because the TRUNCATE TABLE statement resets the AUTO_INCREMENT counter to its starting value.

TRUNCATE TABLE vs DELETE Statement

Although both MySQL statements remove data from the table, they work differently. Look at the differences between them below.

When Should You Avoid TRUNCATE TABLE?

You should avoid using the MySQL TRUNCATE TABLE statement when:

• You want to delete only specific rows in the existing table.
• You need to use a WHERE clause.
• You want to preserve some existing records in the table.
• You need row-by-row deletion behavior.