What does the TRUNCATE
do differently to drop?
I believe it deletes all the data in the table but keeps the table name in the database, where as DROP
deletes all data and the table. Is this correct ?
drop-tableMySQLtruncate
What does the TRUNCATE
do differently to drop?
I believe it deletes all the data in the table but keeps the table name in the database, where as DROP
deletes all data and the table. Is this correct ?
Best Answer
DELETE - DATA MANIPULATION LANGUAGE(DML)
The
DELETE
statement in any RDBMS is considered a DML statement. Also known as CRUD (Create, Read, Update, Delete), this kind of statement is intended to manipulate data in a database without affecting the underlying structure of the objects. What this means in practical term is:DELETE
statement can be fine-tuned using a predicate via eitherWHERE
orJOIN
to delete some or all rows in a table.DELETE
statement will be logged by the database and can be rolled back within a transaction if the statement fails.DELETE
will take row level locks on the data it is deleted, though this could escalate higher as necessary.DELETE
can be "slow" (this is relative), but safer because it is fine-grained.TRUNCATE - DATA DEFINITION LANGUAGE(DDL)
TRUCNATE
is considered a DDL statement, meaning that it is intended to alter how objects are defined in a database. Usually, DDL statements areCREATE
,ALTER
, orDROP
, butTRUNCATE
serves a particular purpose, that of "resettting" a table by removing all the rows. The methods of this differ between the RDBMS engines and I would recommend looking at the specifics of MySQL. The practical implications of aTRUNCATE
are:TRUNCATE
can not be fine grained. If successful, it will remove all the rows from your table.TRUNCATE
is typically not logged. This varies by RDBMS and I would suggest you looks more specifically at how MySQL handles it. (Hint, it varies by version.)TRUNCATE
requires a table metadata lock to execute. How this is actually implemented can be specific to RDBMS, but essentially theTRUNCATE
process must prevent other processes from mucking with the table in order to execute its DDL.TRUNCATE
will be faster than aDELETE
, but less safe.DROP TABLE - DATA DEFINITION LANGUAGE(DDL)
DROP TABLE
goes further than aTRUNCATE
in that it actually removes the table from the database completely. This includes removing all associated objects, such as indexes and constraints. If you dropped a table, not only would you remove all the data, but the structure as well. This will usually be done when a table is no longer needed. The primary concern is that since aDROP
is DDL, you typically can't undo it. Some RDBMS engines will allow you to wrap DDL within a transaction so you can roll it back, but this is not considered best practice and should be avoided.