MySQL delete data Delete and Truncate statements are used for comparison

There are two common methods for empty mysqll table contents: 1 delete and 1 truncate. An delete statement without the where parameter can delete everything in the mysql table, and an truncate table statement can also empty everything in the mysql table. In terms of efficiency, truncate is faster than delete, but after truncate is deleted, mysql log is not recorded and data cannot be recovered.

Its grammatical structure is:

 
TRUNCATE [TABLE] tbl_name 

Here's a simple example,

I want to delete all the records in the friends table by using the following statement:

 
truncate table friends; 

The effect of delete is somewhat similar to deleting all records from the mysql table to the end of the delete, while truncate preserves the structure of the mysql table and recreates the table, with all the states corresponding to the new table.

Grammar:

Definition of the delete statement.

 
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name 

[WHERE where_definition] 

[ORDER BY ...] 

[LIMIT row_count] 

The syntax of MySQL is similar to that of spoken English, and it is enough to indicate which table you want to delete data from and which data you want to delete. Just like when writing narrative articles, time, place, characters, environment and plot elements are indispensable.
Examples are the most vivid and illustrative. So, it's not going to work, just get to the point. Following the syntax above, I want to delete all records in the firends table where user_name is equal to simaopig,
You can use the following SQL statement:

 
delete from friends where user_name = 'simaopig';delete 

Matters needing attention:
From the grammatical structure, we can see that, like update grammar 1, we can omit the where clause. But it's a dangerous thing to do. Because if you don't specify the where clause, delete will delete all the records in the table, and immediately, even if you want to cry, there is no place and no time, because you need to admit your mistake to your supervisor immediately, and immediately find the MySQL log and roll back and forth. But once you've had that experience, I'm sure it's impressive.

The strange thing is that it is almost impossible for me to delete the index file successfully in the case of huge amount of data on innodb and myisam. Leave that to be resolved.

conclusion

While the truncate command spec is fast, it is not as secure for transactions as the delete command. Therefore, if the table we want to perform truncate deletion is transacting, this command will produce an exit and an error message.