Comparison between Delete and Truncate statements for MySQL DATA Deletion

There are two common methods for empty mysqll table content: delete and truncate. The delete statement without the where parameter can delete all content in the mysql table. You can also use the truncate table to clear all content in the mysql table. In terms of efficiency, truncate is faster than delete, but after truncate is deleted, mysql logs are not recorded and data cannot be recovered.

Its syntax structure is:
Copy codeThe Code is as follows:
TRUNCATE [TABLE] tbl_name

Here is a simple example,

To delete all records in the friends table, use the following statement:
Copy codeThe Code is as follows:
Truncate table friends;


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.

Syntax:

The definition of the delete statement.
Copy codeThe Code is as follows:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_definition]

[Order by...]

[LIMIT row_count]


These syntaxes of MySQL are similar to those of the spoken language. You need to specify the table from which you want to delete data and what data to delete. This is enough. Just like when writing a narrative, time, place, character, environment, and plot are essential.
The example is the most vivid and can best illustrate the problem. Therefore, it is useless to go to the topic directly. According to the above syntax structure, I want to delete all records whose user_name is simaopig IN THE firends table,
You can use the following SQL statement:

Copy codeThe Code is as follows:
Delete from friends where user_name = 'simaopig '; delete


Note:
From the syntax structure, we can see that, like the update syntax, We can omit the where clause. However, this is a very dangerous behavior. If the where clause is not specified, delete deletes all records in the table and deletes them immediately. Even if you want to cry, there is no time, because you need to immediately acknowledge the error with the supervisor, and immediately find the MySQL Log, roll back the record. However, once you have had such an experience, I believe it must be impressive.

The strange thing is that when I have a large volume of data on innodb and myisam, and the index file is very large, it is almost impossible to delete it! To be resolved.

Summary

However, the truncate command is fast and fast, but it is not as secure as the delete command for transaction processing. Therefore, if the table to be deleted by truncate is being processed in a transaction, this command will generate an exit and generate an error message.