Two Methods for deleting data in MySQL

There are two ways to DELETE data in MySQL: one is the DELETE statement and the other is the TRUNCATETABLE statement. You can use the WHERE clause to select the record to be deleted. However, using TRUNCATETABLE 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:

There are two ways to DELETE data in MySQL: one is the DELETE statement and the other is the 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:

There are two ways to DELETE data in MySQL: one is the DELETE statement and the other is the 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 standard SQL statements, DELETE supports the ORDER BY and LIMIT clauses, which allow us to 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;