How to delete table data in MySQL: how to delete a table in mysql

How to delete table data in MySQL: how to delete a table in mysql

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, but the execution speed is not fast.

After truncate is deleted, mysql logs are not recorded and data cannot be recovered. The delete operation is like deleting all records in the mysql table one by one, and truncate is equivalent to retaining the structure of the mysql table and re-creating the table. All statuses are equivalent to new tables. 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 LIMIT. 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;

Summary

The above is all about how MySQL deletes table data. If you are interested, you can refer to: MySQL prepare principle details, several important MySQL variables, and analysis of oracle SQL statement optimization technical points. If you have any questions, leave a message at any time, the editor will reply to you in a timely manner.