TRUNCATE in MySQL

1. Definition

  • TRUNCATE is a DDL (Data Definition Language) command used to remove all rows from a table.
  • Unlike DELETE, it is faster and uses less system resources, because:
    • It doesn’t log individual row deletions.
    • It resets the auto-increment counter.

2. Syntax 

TRUNCATE TABLE table_name;

3. Example 

-- Create table
CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

-- Insert data
INSERT INTO employees (name) VALUES ('John'), ('Priya'), ('Ahmed');

-- Truncate the table
TRUNCATE TABLE employees;

-- Now table is empty
SELECT * FROM employees;  -- returns 0 rows

4. TRUNCATE vs DELETE vs DROP

Command Removes Data Auto Increment Reset Rollback (in Txn) Removes Table Structure
TRUNCATE ✅ All rows ✅ Yes ❌ No (non-transactional) ❌ Keeps table
DELETE ✅ Selected / All rows ❌ No ✅ Yes ❌ Keeps table
DROP ✅ All rows N/A ❌ No ✅ Removes table

5. Important Notes

  • You cannot use WHERE with TRUNCATE. It’s all or nothing.
  • Faster than DELETE for large tables.
  • If a table has foreign key constraints, you must disable them before truncating.
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE employees;
SET FOREIGN_KEY_CHECKS = 1;

✅ Summary:

  • TRUNCATE = Fast way to empty a table.
  • Keeps table structure, unlike DROP.
  • No rollback in non-transactional storage engines (like MyISAM).


Back to blog

Leave a comment