Usage of the truncate table statement in mysql

The TRUNCATE command clears all records in the Table in mysql and resets the table ID. Next I will introduce the usage and instance of TRUNCATE.

Truncate table deletes all rows in the TABLE, but does not record the deletion of a single row.

Syntax
Truncate table name;
Parameters
Name (the name of the table to be truncated or the name of the table to delete all its rows)
The truncate table function is the same as the DELETE statement without the 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 a row at a time and records one row in the transaction log. Truncate table deletes data by releasing the data pages used to store TABLE data, and only records the release of pages in transaction logs.
Truncate table deletes all rows in the TABLE, but the TABLE structure, its columns, constraints, and indexes remain unchanged. The Count value used by the new row ID is reset to the seed of the column. To retain the ID Count value, use DELETE instead. To delete TABLE definitions and data, use the drop table statement.

For tables referenced by the foreign key constraint, the truncate table cannot be used, but the DELETE statement without the WHERE clause should be used. Because the truncate table is not recorded in the log, it cannot activate the trigger.
The truncate table cannot be used in the index view.

Example

In the following example, all data in the authors table is deleted.
Truncate table authors;

Permission

The truncate table permission is granted to the TABLE owner, sysadmin fixed server role members, db_owner, and db_ddladmin fixed database role members by default and cannot be transferred.

Differences between delete and truncate

The biggest difference between delete and truncate table is that delete can use the WHERE statement to select the record to be deleted. However, the execution speed is not fast. You can also return the number of deleted records. The truncate table cannot delete the specified record, and the deleted record cannot be returned. But it runs very fast.