MySQL Truncate Table

Last Updated: 18 July, 2023

In MySQL, the TRUNCATE TABLE command is used to delete all the records from a table. This is similar to the DELETE command, without the WHERE clause.

The TRUNCATE TABLE command is more efficient and much faster as compared to the DELETE command because it drops and recreates the table instead of deleting rows one by one.

It also resets the auto-incrementing ID column (if any) to 1. If we want to keep the same ID values and only delete the rows, we should use the DELETE command instead.

The syntax and example for the TRUNCATE TABLE statement are as follows:

Syntax:

  TRUNCATE TABLE [database_name.] table_name;

• database_name: It is an optional, it is the name of the database.
• table_name: The table that we want to truncate.

Example:

  TRUNCATE TABLE school_db.student;

In the TURNCATE TABLE command, the TABLE keyword is optional. However, it is good practice to use the TABLE keyword to distinguish between the TRUNCATE TABLE statement and the TRUNCATE() function.

Let's understand the TURNCATE TABLE statement with the help of the given example.

Suppose we have a table called employees with the following structure:

Table: employees

  mysql> select * from employees;
  +----+-----------+------------+--------+------------+---------------+
  | ID | Name      | Department |   Age  |   Salary   |  JoiningDate  | 
  +----+-----------+------------+--------+------------+---------------+
  | 1  | Rajesh    | Sales      |   43   |    54,000  |   2023-06-11  |
  | 2  | Kamal     | HR         |   35   |    38,000  |   2021-01-23  |
  | 3  | Vimal     | Sales      |   52   |    68,000  |   2022-04-08  |
  | 4  | Hina      | Marketing  |   38   |    72,000  |   2018-08-16  |
  | 5  | Jyanti    | Finance    |   29   |    60,000  |   2010-02-24  |
  +----+-----------+------------+--------+------------+---------------+

Now, truncating the employees table.

  mysql> truncate table employees;
  Query OK, 0 rows affected (1.38 sec)

  mysql> select * from employees;
  Empty set (0.05 sec)

Here, TRUNCATE TABLE statement deleted all the records from the employees table.

The TRUNCATE TABLE statement is a powerful command that can permanently delete records and cannot be rolled back, so always make sure we have a backup of our data before using this command.