MySQL Delete table data: The difference between delete and TRUNCATE

There are two ways to delete data in MySQL, one is a DELETE statement and the other is a TRUNCATE TABLE statement. The DELETE statement can select the record to be deleted by where. Using TRUNCATE TABLE will delete all records in the table. Therefore, the DELETE statement is more flexible.

If you want to clear all the records in a table, you can use the following two methods:

DELETE from Tb_mail
TRUNCATE TABLE Tb_mail

The table in the second record is optional.

If you want to delete some of the records in a table, you can only use the DELETE statement.

DELETE from table1 WHERE ...;

If Delete does not have a WHERE clause, then 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.

If there is a self-increment field in a table, the self-increment field restores the starting value to 1 when all records are deleted using TRUNCATE TABLE and delete without a WHERE clause. 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.

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.

Unlike standard SQL statements, delete supports both the order BY and the limit clauses, and with these two clauses, we have more control over the records to be deleted. If we want to delete only a portion of the records filtered by the WHERE clause, you can use Limib, which can be used with order by and limit if you want to delete the latter few records. Suppose we want to delete the first 6 records in the users table that have name equal to "Mike". You can use the following DELETE statement:

DELETE from users WHERE name = ' Niubi ' LIMIT 6;

In general, MySQL does not determine which 6 of the 6 records were deleted, and we can use order by to sort the records in order to be more insured.

DELETE from the users WHERE name = ' Niubi ' ORDER by ID DESC LIMIT 6;tb_mail

MySQL Delete table data: The difference between delete and TRUNCATE