MySQL TRUNCATE TABLE Statement

MySQL TRUNCATE TABLE Statement

The MySQL TRUNCATE TABLE statement is used to remove all rows from a table quickly, without logging individual row deletions. This statement is essential for resetting table data efficiently.

Syntax

TRUNCATE TABLE table_name;

The TRUNCATE TABLE statement has the following component:

  • table_name: The name of the table from which all rows will be removed.

Example MySQL TRUNCATE TABLE Statement

Let's look at an example of the MySQL TRUNCATE TABLE statement and how to use it:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

This query creates a table named employees with columns for id, first_name, last_name, and email.

MySQL CREATE TABLE

Step 3: Inserting Data into the Table

Insert some data into the table:

INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com'),
       ('Jane', 'Smith', 'jane.smith@example.com');

This query inserts two rows into the employees table.

MySQL INSERT INTO TABLE

Step 4: Truncating the Table

Truncate the table:

TRUNCATE TABLE employees;

This query removes all rows from the employees table, effectively resetting the table.

MySQL TRUNCATE TABLE

Step 5: Verifying Table Truncation

To verify that the table has been truncated, you can select all rows from the table:

SELECT * 
FROM employees;

This query retrieves all rows from the employees table. The result should be an empty set.

MySQL SELECT FROM TRUNCATED TABLE

Conclusion

The MySQL TRUNCATE TABLE statement is a powerful tool for quickly removing all rows from a table. Understanding how to use the TRUNCATE TABLE statement is essential for efficient database management in MySQL.