MySQL: TRUNCATE TABLE Statement

This tutorial explains how to use the MySQL TRUNCATE TABLE statement with syntax and examples.

Description

The TRUNCATE TABLE statement is used to remove all records from a table in MySQL. It performs the same function as a DELETE statement without a WHERE clause.

Syntax

The syntax for the TRUNCATE TABLE statement in MySQL is:

TRUNCATE TABLE [database_name.]table_name;

Parameters or Arguments

database_name

Optional. If specified, it is the name of the database.

table_name

The table that you wish to truncate.

Note

• When you truncate a table, the AUTO_INCREMENT counters on the table will be reset.
• MySQL truncates the table by dropping and creating the table. Thus, the DELETE triggers for the table do not fire during the truncation.
• Starting in MySQL 5.5, you can not truncate an InnoDB table that is referenced by a foreign key in another table.
• Starting in MySQL 5.6, you can not truncate a NDB table that is referenced by a foreign key in another table.

Example

In MySQL, truncating a table is a fast way to clear out records from a table if you don't need to worry about rolling back. In most cases, MySQL handles the process of table truncation a bit differently than other databases such as Oracle or SQL Server.

Let's look at an example of how to use the TRUNCATE TABLE statement in MySQL.

For example:

TRUNCATE TABLE customers;

This example would truncate the table called customers and remove all records from that table.

It would be equivalent to the following DELETE statement in MySQL:

DELETE FROM customers;

Both of these statements would result in all data from the customers table being deleted. The main difference between the two is that you can roll back the DELETE statement if you choose, but you can't roll back the TRUNCATE TABLE statement.

Let's look at one more example where we prefix the table name with the database name.

For example:

TRUNCATE TABLE dbd.products;

This example would truncate the table called products in the database called dbd.