MySQL TRUNCATE

The TRUNCATE function in MySQL is used to quickly delete all rows from a table and reset the auto-increment counter, if the table has an auto-increment column. It is a faster and more efficient alternative to the DELETE statement when you want to remove all records from a table. Unlike DELETE, which removes rows one by one and logs individual row deletions, TRUNCATE is a DDL (Data Definition Language) statement that deallocates the data pages for the table, making it faster but less flexible.

Syntax

The syntax for the TRUNCATE statement is straightforward:

TRUNCATE [TABLE] table_name;

TABLE: Optional keyword; you can use it, but it’s not necessary.

table_name: The name of the table from which you want to delete all rows.

Example

Let’s consider a simple example where you have a table named employees:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50)
);

INSERT INTO employees (name, position) VALUES 
('John Doe', 'Manager'),
('Jane Smith', 'Developer'),
('Bob Johnson', 'Analyst');

Now, if you want to remove all records from the employees table using TRUNCATE, you would do:

TRUNCATE TABLE employees;

After executing this statement, all rows in the employees table will be deleted, and the auto-increment counter will be reset. Keep in mind that this operation is irreversible, and you won’t be able to roll back the changes.

It’s important to note that TRUNCATE is more efficient than DELETE for large tables, but it has some limitations. For instance, you cannot use a WHERE clause with TRUNCATE, and it doesn’t trigger any ON DELETE triggers. Therefore, if you need more control or want to conditionally delete specific records, you should use the DELETE statement instead.