The difference between a MySQL DELETE statement and a TRUNCATE TABLE statement

The difference between a MySQL DELETE statement and a TRUNCATE TABLE statement 2010-10-08 16:05 Anonymous InternetSize:T|T

In the MySQL database, both the DELETE statement and the TRUNCATE TABLE statement can be used to delete data, but there are differences between the two statements, which are described below.

AD: Dry Goods come, don't wait! WOT2015 Beijing Station lecture ppt open download!

The MySQL DELETE statement functions similarly to the TRUNCATE TABLE statement, but what is the difference between the two? The differences between the MySQL DELETE statement and the TRUNCATE TABLE statement are analyzed below for your reference.

There are two ways to delete data in MySQL, one is the MySQL DELETE statement and the other is the MySQL 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 table1
TRUNCATE TABLE table1
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, the MySQL DELETE statement 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 = ' Mike ' 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 = ' Mike ' ORDER by ID DESC LIMIT 6;

Source: >  

From for notes (Wiz)

The difference between a MySQL DELETE statement and a TRUNCATE TABLE statement