MySQL TRUNCATE TABLE

Introduction

In this chapter, we will learn how to use the TRUNCATE TABLE statement in MySQL. Truncating a table removes all rows from the table, but unlike the DROP TABLE statement, it does not delete the table itself. This operation is faster than deleting rows one by one, and it resets any auto-increment counters. We will cover the syntax and examples for using the TRUNCATE TABLE statement.

Truncating a Table

To truncate a table, we use the TRUNCATE TABLE statement. This command removes all rows from the specified table, effectively emptying it.

Syntax

TRUNCATE TABLE table_name;

• table_name: The name of the table you want to truncate.

Example

TRUNCATE TABLE employees;

This example removes all rows from the employees table.

Full Example

Let's go through a full example where we create a table, insert some data, truncate the table, and then verify that the table has been emptied.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create a Table:

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

4. Insert Data into the Table:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02');

5. Verify the Data Insertion:

SELECT * FROM employees;

Output

id	first_name	last_name	email	hire_date
1	Rahul	Sharma	rahul.sharma@example.com	2023-07-01
2	Priya	Singh	priya.singh@example.com	2023-07-02

6. Truncate the Table:

TRUNCATE TABLE employees;

7. Verify the Table Truncation:

SELECT * FROM employees;

Output

id	first_name	last_name	email	hire_date

Important Considerations

• Irreversibility: Truncating a table is permanent and cannot be undone. Make sure you really want to remove all data from the table before executing the command.
• Performance: TRUNCATE TABLE is faster than DELETE FROM table_name because it does not generate individual row delete operations.
• Auto-Increment Reset: Truncating a table resets any auto-increment counters.
• Permissions: You need the appropriate privileges to truncate a table. Typically, this requires DROP and ALTER privileges on the table.

Conclusion

Truncating a table in MySQL is a quick way to remove all rows from a table while preserving the table structure. This chapter covered how to use the TRUNCATE TABLE statement and provided a full example to illustrate the process.