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.

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.

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.

Step 4: Truncating the Table

Truncate the table:

TRUNCATE TABLE employees;

This query removes all rows from the employees table, effectively resetting the 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.

---

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.