Truncating and deleting are two commands used to remove data from a table. Delete is a DML command, while truncate is a DDL command. Truncate can be used to delete all data from the table without maintaining the integrity of the table. On the other hand, the delete statement can be used to delete specific data and cannot bypass integrity enforcement mechanisms.
Remapping TRUNCATE TABLE to DROP TABLE and CREATE TABLE in MySQL 8.0 prevents troublesome scanning of LRU. At a high level, you might consider the truncate command to be similar to a Delete command without a Where clause. TRUNCATE TABLE operations on tables that use a DDL-compliant atomic storage engine are fully committed or rolled back if the server is stopped during its operation. TRUNCATE TABLE fails for an InnoDB table or an NDB table if there are FOREIGN KEY constraints from other tables that reference the table.
Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows or a sequence of DROP TABLE and CREATE TABLE statements. TRUNCATE TABLE can be used with performance schema summary tables, but the effect is to reset summary columns to 0 or NULL, not delete rows. When used with partitioned tables, TRUNCATE TABLE preserves the partition; that is, data files and indexes are deleted and re-created, while partition definitions are not affected. TRUNCATE TABLE is treated for binary logging and replication purposes as DROP TABLE followed by CREATE TABLE, that is, as DDL instead of DML.
Leave Reply