MySQL Data Deletion Methods: DELETE, TRUNCATE, and DROP

MySQL provides three common ways to delete data: the DML statement DELETE , the DDL statements TRUNCATE and DROP . Although all can remove data, they differ in speed and behavior.

1. Execution speed

drop > truncate >> DELETE

2. DELETE

DELETE from TABLE_NAME where xxx

DELETE is a DML operation that removes rows without affecting table structure, participates in transactions, and fires triggers. In InnoDB, rows are only marked as deleted; the physical space is not released until an OPTIMIZE TABLE is run. The deleted rows are stored in the rollback segment and generate redo/undo logs.

When deleting all rows ( DELETE from table_name ) MyISAM releases disk space immediately, while InnoDB does not. Conditional deletes never release space for either engine. Running OPTIMIZE TABLE table_name frees space for both engines.

2. TRUNCATE

TRUNCATE table TABLE_NAME

TRUNCATE is a DDL operation, does not use transactions, does not generate rollback entries, and does not fire triggers. It instantly releases disk space for both InnoDB and MyISAM, effectively works like a fast DROP followed by a CREATE of the same table, and resets the auto_increment counter.

Special notes for storage engines:

MyISAM: TRUNCATE resets auto_increment to 1; DELETE leaves the counter unchanged.

InnoDB: TRUNCATE also resets auto_increment to 1. After a DELETE of all rows, the counter persists until the server restarts, after which it may reset.

Use TRUNCATE with caution, especially without a backup.

3. DROP

DROP table Tablename

DROP is also a DDL command; it removes the table definition, associated constraints, triggers, and indexes, and instantly frees disk space for both InnoDB and MyISAM. Dependent stored procedures become invalid but remain in the database.

As a metaphor: DELETE tears out the book's index, TRUNCATE burns the book’s contents, and DROP burns the entire book.