DROP and TRUNCATE behavior in MySQL

Introduction

DROP and TRUNCATE help you remove data or tables quickly and cleanly from your database.

When you want to delete all rows from a table but keep the table structure for future use.

When you want to completely remove a table and all its data from the database.

When you need to reset a table to empty without logging each row deletion.

When cleaning up test data after experimenting with a table.

When you want to free up space by removing unused tables.

Syntax

MySQL

DROP TABLE table_name;
TRUNCATE TABLE table_name;

DROP TABLE removes the entire table and its data permanently.

TRUNCATE TABLE deletes all rows but keeps the table structure for reuse.

Examples

This command deletes the 'employees' table and all its data permanently.

MySQL

DROP TABLE employees;

This command removes all rows from 'employees' but keeps the table ready for new data.

MySQL

TRUNCATE TABLE employees;

This safely drops the 'employees' table only if it exists, avoiding errors.

MySQL

DROP TABLE IF EXISTS employees;

Sample Program

This example creates a table, adds two rows, shows them, then truncates the table to remove all rows, shows the empty table, drops the table completely, and finally checks that the table no longer exists.

MySQL

CREATE TABLE test_table (id INT, name VARCHAR(20));
INSERT INTO test_table VALUES (1, 'Alice'), (2, 'Bob');
SELECT * FROM test_table;
TRUNCATE TABLE test_table;
SELECT * FROM test_table;
DROP TABLE test_table;
SHOW TABLES LIKE 'test_table';

Important Notes

TRUNCATE is faster than DELETE without WHERE because it does not log individual row deletions.

DROP removes the table structure, so you cannot insert data after dropping without recreating the table.

Be careful: both DROP and TRUNCATE cannot be undone easily, so always double-check before running.

Summary

DROP deletes the whole table and its data permanently.

TRUNCATE removes all rows but keeps the table structure.

Use TRUNCATE to quickly empty a table, and DROP to remove it completely.