How to Truncate a MySQL Table

Truncating a MySQL table is a useful way to delete all the data in a table without removing its structure. The TRUNCATE TABLE statement is part of the data definition language (DDL) command and allows you to delete all the records from a table in MySQL. It performs the same function as a DELETE statement without a WHERE clause. The TRUNCATE () function truncates a number to the specified number of decimal places.

Be very careful when using this command, as it will empty the contents of the MySQL table and cannot be undone. This is useful in development, but you rarely want to do it in production. 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. The TRUNCATE command is more efficient compared to the DELETE command because it deletes and recreates the table rather than deleting the individual records one at a time.

In most cases, MySQL handles the table truncation process differently than other databases, such as Oracle or SQL Server. Before using the TRUNCATE statement, you must set the FOREIGN_KEY_CHECK variable to False because it fails if the table contains restrictions from other tables. If there are any FOREIGN KEY constraints from other tables that refer to the table being truncated, the TRUNCATE TABLE statement will fail. As long as the table definition is valid, however, the table can be recreated as an empty table with TRUNCATE TABLE, even if the data or index files have been corrupted. MySQL TRUNCATE () returns a number after truncating it to certain decimals.

The number and number of decimals are specified as arguments to the TRUNC function. When used with Performance Schema summary tables, TRUNCATE TABLE has no effect on rows but instead resets summary columns to 0 or NULL. TRUNCATE TABLE is classified as a DDL statement rather than a DML statement and is similar to a DELETE statement without a WHERE clause that deletes all rows or a sequence of DROP TABLE and CREATE TABLE statements.