Truncate TABLE statement usage in MySQL

TRUNCATE table deletes all rows in a table without recording a single row delete operation.

Grammar
TRUNCATE TABLE name;
Parameters
Name (the names of the tables you want to truncate or the names of the tables whose rows you want to delete)
TRUNCATE table is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources.
The DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data pages used to store the table data, and only records the release of the page in the transaction log.
TRUNCATE table deletes all rows in the tables, but the table structure and its columns, constraints, indexes, and so on remain unchanged. The count value used for the new line identification is reset to the seed of the column. If you want to keep the identity count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP table statement.

For tables referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table, but you should use a DELETE statement without a WHERE clause. Because the TRUNCATE TABLE is not logged in the log, it cannot activate the trigger.
TRUNCATE table cannot be used for tables that participate in indexed views.

Example

The following example deletes all the data in the authors table.
TRUNCATE TABLE authors;

Permissions

The TRUNCATE table permission is granted to table owners, members of the sysadmin fixed server role, db_owner and db_ddladmin fixed database role members and are not transferable

About the difference between delete and truncate

The biggest difference between delete and TRUNCATE TABLE is that delete can select the record to delete through the where statement. But the speed of execution is not fast. You can also return the number of records that were deleted. The TRUNCATE TABLE cannot delete the specified record, and the deleted record cannot be returned. But it executes very fast.