Delete and trncate differences in MySQL

Delete table records in MySQL the usage difference between the deletes from and TRUNCATE TABLE:
There are two ways to delete records in a table in MySQL:
(1) Delete from statement,
(2) TRUNCATE TABLE statement.

The delete from statement can use where to select the record to be deleted. The DELETE statement is more flexible.
TRUNCATE TABLE will delete all records in the table.

Situation one: Emptying all the records in the table, you can use the following two methods:
Delete from TableName
TRUNCATE TABLE TableName
The table in the second record is optional.

Case two: Delete some of the records in the table and use only the DELETE statement.
Delete from table1 WHERE;

Difference:
(1) If Delete does not add a WHERE clause, it is the same as TRUNCATE TABLE, but they are a little different, that is, the delete can return the number of records deleted, and TRUNCATE TABLE returns 0.

(2) If there are self-increment fields in a table, and delete all records using TRUNCATE TABLE and delete without a WHERE clause, the self-increment field restores the starting value to 1. If you do not want to do this, you can add a permanent where in the DELETE statement, such as where 1 or where true.
Delete from table1 WHERE 1;

The above statement scans each record as it executes. But it is not compared, because this where condition is always true. This can maintain the maximum value of the increment, but because it scans all records, it is much more expensive to execute than delete without a WHERE clause.

(3) Another point is that if you want to delete all the data in the table, it is recommended to use TRUNCATE TABLE, especially if there is a large amount of data in the table, and using TRUNCATE TABLE is a lot faster than using the delete from, and the delete From is a one-line deletion, which is very slow.

Summarize:
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 was unpleasant. You can also return the number of records that were deleted. Truncate TABLE cannot delete the specified record, and the deleted record cannot be returned. But it executes very fast.

Delete:
Unlike standard SQL statements, delete supports the ORDER BY and limit clauses.
Delete from the users WHERE name = ' Nike ' ORDER by ID DESC LIMIT 6;

Delete and trncate differences in MySQL