How to use MySQL truncate to remove whole table data

MySQL truncate statement

If you require removing whole table data from MySQL tables, you can use the TRUNCATE statement. The Truncate command will remove whole data from the given table.

Note that, only table data will be removed while table structure, its fields, and constraints will not be affected. That means you can use the insert statement to enter data after using the Truncate statement.

The delete statement of MySQL is also used to remove data from the tables. However, you can remove single or more records by using the delete statement.

You can also use delete statement to remove whole table data just like TRUNCATE statement e.g. “delete from table_name”. However, the truncate statement is faster in execution.

If you have used any auto-increment key in the table then MySQL TRUNCATE also resets it back to 1.

The Truncate syntax

This is how you can use the truncate command in MySQL:

TRUNCATE TABLE table_name;

An example of truncate table

The following example will remove whole table data by using the Truncate command. For the demo, we have created a table, tbl_employee. You can see data before and after using Truncate statement.

The Truncate query will be:

You can see table data is removed after using MySQL Truncate while table structure still exists.

If you need to remove data as well as table structure then use the drop table statement.

Also see MySQL delete

Was this article helpful?

Related Articles

Leave A Comment?