How to Truncate All Data from a MySQL Table

08/04/2021

Contents

In this article, you will learn how to truncate all data from a MySQL table.

Truncating a MySQL table

Truncating a MySQL table is a fast way to delete all data from the table, without deleting the table structure itself.

Connect to MySQL

Before we can truncate a MySQL table, we need to connect to the MySQL server using a client like MySQL Workbench, phpMyAdmin or through command line.

Identify the table to truncate

Once you are connected to the MySQL server, identify the table that you want to truncate. Use the following command to view all tables in the current database:

SHOW TABLES;

Backup the data

Before truncating a table, it is important to backup the data to avoid losing any valuable information. This can be done using the mysqldump command.

Use the TRUNCATE statement

To truncate a MySQL table, use the TRUNCATE statement. The TRUNCATE statement deletes all data from the table and resets any auto-increment values to 1.

TRUNCATE table_name;

The table_name is the name of the table that you want to truncate.

Example

TRUNCATE users;

This will delete all data from the users table.

Confirm the data has been truncated

Once you have executed the TRUNCATE statement, confirm that the data has been truncated by querying the table.

#MySQL