MySQL TRUNCATE TABLE

The TRUNCATE TABLE statement in MySQL is used to quickly and completely delete all rows from a table. Unlike the DELETE statement, which removes rows one at a time and logs each deletion, TRUNCATE is more efficient as it deallocates the data pages and resets the table.

Syntax

TRUNCATE TABLE table_name;

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

Key Features of `TRUNCATE TABLE`

Removes All Rows: Deletes all rows in the table without logging individual row deletions.
Resets AUTO_INCREMENT: Resets the AUTO_INCREMENT counter to zero (or the defined start value).
Cannot Roll Back: The operation is non-transactional and cannot be rolled back.
Faster than DELETE: Since it doesn't generate individual row logs, it's faster than DELETE for large datasets.
Permissions Required: Requires DROP or ALTER privileges on the table.

Examples

1. Truncating a Table

Suppose you have a table named employees:

TRUNCATE TABLE employees;

This command removes all rows from the employees table and resets the AUTO_INCREMENT value.

Differences Between `TRUNCATE` and `DELETE`

Feature	`TRUNCATE TABLE`	`DELETE`
Removes Rows	All rows at once	Row-by-row basis
Speed	Faster (deallocates pages)	Slower (logs each row deletion)
Auto-Increment Reset	Yes	No
Transaction Support	No	Yes (can roll back)
Foreign Key Constraints	Restricted in child-referenced	Enforced
Logging	Minimal	Full row-by-row logging

Limitations

Foreign Key Constraints:
- You cannot truncate a table that is referenced by a foreign key unless the referencing foreign key constraints are disabled.
- Use SET FOREIGN_KEY_CHECKS=0 before truncating such tables.
Example:
```
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE employees;
SET FOREIGN_KEY_CHECKS = 1;
```
Non-Transactional:
- Once you execute TRUNCATE TABLE, it cannot be undone, even if the session is within a transaction.

Best Practices

Backup Data: Ensure you have a backup before truncating critical tables.
Check Constraints: Disable foreign key checks if truncating a child-referenced table.
Use for Large Data: Prefer TRUNCATE TABLE over DELETE when dealing with large datasets for faster performance.

Conclusion

The TRUNCATE TABLE statement is a fast and efficient way to delete all rows from a table, reset the table's data structure, and reclaim storage space. However, due to its non-transactional nature and inability to be rolled back, it should be used with caution in production environments. Always ensure proper backups and verify dependencies before truncating a table.

Souy Soeng

MySQL TRUNCATE TABLE