How to delete table data in MySQL

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

If you want to clear all 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 records in a table, you can only use the DELETE statement.

 DELETE FROM table1 WHERE ...;

If DELETE does not have a WHERE clause, it is the same as TRUNCATE TABLE, but there is one difference between them, that is, DELETE can return the number of deleted records, while TRUNCATE TABLE returns 0.

If a table has an auto-increment field, after deleting all records using TRUNCATE TABLE and DELETE without a WHERE clause, the starting value of the auto-increment field will be restored to 1. 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 will scan every record when executed. But it does not compare, because this WHERE condition is always true. Although this can maintain the maximum value of the auto-increment, it scans all records, so its execution cost is much greater than DELETE without a WHERE clause.

The biggest difference between DELETE and TRUNCATE TABLE is that DELETE can select the records to be deleted through the WHERE statement, but the execution speed is not fast.

After truncate is deleted, MySQL log is not recorded and data cannot be restored. The effect of delete is a bit like deleting all the records in the MySQL table one by one until all are deleted, while truncate is equivalent to retaining the structure of the MySQL table and recreating the table, and all states are equivalent to the new table. It can also return the number of deleted records. However, TRUNCATE TABLE cannot delete the specified records and cannot return the deleted records. But it executes very quickly.

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

   DELETE FROM users WHERE name = 'Mike' LIMIT 6;

Generally, MySQL is not sure which 6 records are deleted. To be safe, we can use ORDER BY to sort the records.
DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6;

Summarize

The above is all the content of this article about how to delete table data in MySQL. I hope it will be helpful to everyone. Interested friends can refer to: Detailed explanation of MySQL prepare principles, several important MySQL variables, analysis of key points of ORACLE SQL statement optimization technology, etc. If you have any questions, you can leave a message at any time and the editor will reply to you in time.