When it comes to deleting data, the TRUNCATE TABLE statement is often the go-to option for many users. It is a fast and efficient way to delete all the data in a table, but it does have some limitations. One of the most important things to note is that TRUNCATE TABLE cannot be used when a foreign key references the table to be truncated, because truncate TABLE statements do not activate triggers. Unlike the DELETE statement, which does trigger triggers, TRUNCATE statements do not.
Logically, truncating TABLE is similar to a DELETE statement that deletes all rows or a sequence of DROP TABLE and CREATE TABLE statements. For high performance, it bypasses the DML method of deleting data. Therefore, it cannot be undone, it does not cause ON DELETE triggers to fire, and it cannot be done for InnoDB tables with parent-child foreign key relationships. Triggers can be configured to execute as part of any combination of INSERT, UPDATE, and DELETE statements. Often, actions taken by the trigger only need to occur in certain scenarios where specific columns have been affected.
This post will explain how to make that determination by creating a table with a trigger and executing a series of scenarios. TRUNCATE TABLE removes data by demapping the data pages used to store the table data and records only page deallocations in the transaction log. Logically, TRUNCATE TABLE is equivalent to a DELETE statement that deletes all rows, but there are practical differences in some circumstances. When used with partitioned tables, TRUNCATE TABLE preserves the partition; that is, data and index files are removed and recreated, while partition definitions are not affected. For users of the InnoDB plug-in, space is automatically reclaimed, as described in Reclaiming Disk Space with TRUNCATE TABLE. If there are no FOREIGN KEY constraints, InnoDB performs a quick truncation by removing the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one.
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. Truncation is not possible when the table is referred to by a foreign key or when the table is used for replication or with an indexed view. Truncation operations discard and recreate the table, which is much faster than deleting rows one by one, especially for large tables. Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. In the event that you want all the data in the table to be deleted and the counter reset to 1, truncation can help you.
TRUNCATE TABLE fails for an InnoDB table or an NDB table if there are FOREIGN KEY constraints from other tables that reference the table. TRUNCATE TABLE deletes the data by de-mapping the data pages used to store the table data and only records page deallocations in the transaction log.
Leave Reply