MySQL TRUNCATE() Function

 The TRUNCATE() function in MySQL is used to delete all rows from a table, effectively emptying the table while retaining its structure (i.e., the table definition, such as column names and types, remains intact). It is faster than the DELETE statement because it does not log individual row deletions, and it also resets any auto-increment values to their starting point.

Syntax:

TRUNCATE TABLE table_name;

Key Points:

• Faster than DELETE: TRUNCATE is generally faster than DELETE because it does not scan each row and write to the transaction log for each row deletion.
• Cannot be rolled back: Unlike DELETE, which can be rolled back if wrapped in a transaction, TRUNCATE is a DDL operation and is not transaction-safe. Once executed, the changes cannot be undone unless a backup exists.
• Resets auto-increment: It resets any auto-increment values back to the initial value (usually 1).
• Cannot have WHERE conditions: TRUNCATE affects the entire table and does not allow specifying conditions (unlike DELETE).
• No triggers: TRUNCATE does not activate any DELETE triggers that may be defined on the table.

Example:

TRUNCATE TABLE employees;

This command will remove all rows from the employees table but leave the table structure intact.

When to use TRUNCATE:

• When you need to quickly clear out a table.
• When you do not need to worry about triggering delete actions or auditing changes on each individual row.
• When you don't need to worry about rolling back the operation.

It’s important to use this function carefully since it completely removes all data from the table.