Differences between MySQLDELETE statements and TRUNCATETABLE statements

In MySQL databases, both the DELETE statement and the TRUNCATETABLE statement can be used to DELETE data, but the two statements are different. The following describes the differences between the two statements. The functions of MySQLDELETE and TRUNCATETABLE are the same, but what is the difference between them? The MySQLDELETE statement and TRUNC

In MySQL databases, both the DELETE statement and the truncate table statement can be used to DELETE data, but the two statements are different. The following describes the differences between the two statements. The functions of the MySQL DELETE statement and the truncate table statement are the same, but what is the difference between the two? The MySQL DELETE statement and TRUNC

In MySQL databases, both the DELETE statement and the truncate table statement can be used to DELETE data, but the two statements are different. The following describes the differences between the two statements.

The MySQL DELETE statement and the truncate table statement have similar functions, but what is the difference between them? The following describes the differences between the MySQL DELETE statement and the truncate table statement for your reference.

There are two ways to DELETE data in MySQL: MySQL DELETE statement and MySQL truncate table statement. You can use the WHERE clause to select the record to be deleted. However, using truncate table will delete all records in the TABLE. Therefore, the DELETE statement is more flexible.

To clear all records in the table, you can use the following two methods:

Delete from table1
Truncate table table1
The TABLE in the second record is optional.

To DELETE some records in a table, you can only use the DELETE statement.

Delete from table1 WHERE;
If DELETE does not add a WHERE clause, it is the same as the truncate table, but they are a little different. That is, DELETE can return the number of deleted records, while the truncate table returns 0.

If an auto-increment field exists in a TABLE, after deleting all records using truncate table and DELETE without WHERE clause, the auto-increment field restores the starting value to 1. if you do not want to do this, you can add the permanent WHERE statement in the DELETE statement, such as WHERE 1 or WHERE true.

Delete from table1 WHERE 1;
The preceding statement scans each record during execution. But it is not compared, because the WHERE condition is always true. In this way, although the maximum auto-increment value can be maintained, because it scans all records, the execution cost is much greater than the DELETE without the WHERE clause.

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.

Unlike the standard SQL statement, the MySQL DELETE Statement supports the ORDER BY and LIMIT clauses. With these two clauses, we can better control the records to be deleted. For example, if you only want to delete part of the records filtered out BY the WHERE clause, you can use LIMIB. If you want to delete the last few records, you can use order by and LIMIT together. Suppose we want to delete the first six records whose name is "Mike" in the users table. You can use the following DELETE statement:

Delete from users WHERE name = 'Mike 'LIMIT 6;
Generally, MySQL does not determine which 6 records are deleted. To be more secure, we can use order by to sort the records.

Delete from users WHERE name = 'Mike 'order by id desc limit 6;