The truncate statement in MySQL is a part of the Data Definition Language (DDL) command that allows users to delete all data from a table without deleting its structure. This command is used when users want to delete all data from a table without deleting the table itself. The TRUNCATE TABLE statement performs the same function as a DELETE statement without a WHERE clause. It is much faster than deleting rows one by one, especially for large tables.
The TRUNCATE () function truncates a number to the specified number of decimal places. It returns a number after being truncated to certain decimals. The number and number of decimal places are specified as arguments to the TRUNCATE function. Be aware that this command cannot be undone, so it should be used with caution.
It is useful in development, but rarely in production. To have TRUNCATE TABLE create the tablespace in its current location, add the directory to the innodb_directories configuration before running TRUNCATE TABLE. The TRUNCATE TABLE statement does not trigger DELETE triggers associated with the table being truncated. If there are FOREIGN KEY constraints on other tables that reference the table being truncated, the TRUNCATE TABLE statement will fail.
We first set the FOREIGN_KEY_CHECK variable to False because the TRUNCATE statement fails if the table contains constraints from other tables. Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows or a sequence of DROP TABLE and CREATE TABLE statements. Remapping TRUNCATE TABLE to DROP TABLE and CREATE TABLE in MySQL 8.0 prevents troublesome scanning of LRU. However, it is recommended that you use the TABLE keyword to distinguish between the TRUNCATE TABLE statement and the TRUNCATE () function. In most cases, MySQL handles the process of table truncation differently than other databases such as Oracle or SQL Server.
We became familiar with the differences between the TRUNCATE TABLE and DELETE commands, and also explored some practical examples to clarify the concepts.
