MySQL deleted data Delete vs. truncate statement usage comparison

In the MySQL database, both the DELETE statement and the TRUNCATE TABLE statement can be used to delete data, but there are differences between the two statements, the following shows you the difference between the empty MYSQLL table content is common in two ways: a Delete, One is truncate. The DELETE statement without the Where parameter removes everything from the MySQL table, and you can also empty all the contents of the MySQL table using TRUNCATE table. The efficiency of truncate is faster than delete, but the MySQL log is not recorded after Truncate deleted, and data cannot be recovered.

The syntax structure is: Copy code code as follows: TRUNCATE [TABLE] Tbl_name

Here's a simple example,

I want to delete all the records in the Friends table, you can use the following statement: Copy the code as follows: TRUNCATE TABLE friends;

The effect of delete is a bit like removing all records from a MySQL table to the end of a delete, and truncate is equivalent to preserving the structure of the MySQL table, recreating the table, all of which are equivalent to the new table.

Grammar:

The definition of the DELETE statement. Copy the code code as follows: DELETE [low_priority] [QUICK] [IGNORE] from Tbl_name

[WHERE Where_definition]

[ORDER by ...]

[LIMIT Row_count]

MySQL's grammar is similar to spoken language, you have to point out which table you want to delete data from, and what data to delete, that's enough. Just like when writing a narrative, time, place, character, environment, and the elements of the plot are essential. Examples are the most vivid and most illustrative of the problems. So, not the whole useless, directly into the subject. By following the syntax structure above, I want to delete all records in the Firends table that are equal to Simaopig, and I can use the following SQL statement:

Copy the code code as follows: Delete from friends where user_name = ' Simaopig ';d elete

Note: From the syntax structure, we can see that, as with the update syntax, we can omit the WHERE clause. But this is a very dangerous behavior. Because if you do not specify a WHERE clause, delete deletes all records in the table and deletes them immediately, even if you want to cry without a place, and there is no time, because you need to immediately acknowledge the error with the supervisor and immediately find the MySQL log and roll back the logs. But once you have this experience, I believe it must be impressive.

Oddly enough, I'm on InnoDB and MyISAM. Large data volume, the index file is extremely unlikely to delete success! To be resolved.

Summarize

However, the truncate command is fast, but it is not safe for transactions like the Delete command. Therefore, if we want to execute a truncate deleted table that is being transacted, this command generates an exit and generates an error message

MySQL deleted data Delete vs. truncate statement usage comparison