SQL truncate and delete usage and difference

You need to understand the Delete statement of MySQL. MySQL provides the delete and truncate statements to delete data.

Definition of the delete statement:
Most of the children who often deal with databases use delete statements to delete data. Now let's take a look at the definition of the delete statement.
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_definition]

[Order by...]

[LIMIT row_count] some people may have to ask: "You are too lazy to copy the syntax structure in the manual. Why not write the syntax structure for deleting multiple tables? "My answer is" this is a blog website. It cannot cover all aspects. It can only list the most commonly used syntaxes. For more advanced things, we may introduce them later, but now it is not discussed in this log ", YY is complete.

Example of a delete statement:

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 preceding syntax structure, to delete all records whose user_name is simaopig in the firends table, you can use the following SQL statement:

Delete from friends where user_name = 'simaopig ';

Delete considerations:

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.

A brief description of the truncate statement:

I have never touched or used this statement before. Generally, delete statements are used to delete data.
In fact, this truncate command is very simple. It means to delete all records in the table. It is equivalent to a delete statement that does not write a where clause. Its syntax structure is:
TRUNCATE [TABLE] tbl_name here is a simple example. To delete all records in the friends TABLE, use the following statement:

Truncate table friends;

Efficiency of truncate and delete:
To delete all data in a table, the truncate statement is faster than the delete statement. Because truncate deletes the table and creates it again based on the table structure, delete deletes records and does not try to modify the table. This is also why MySQL will remember the previously generated AUTOINCREMENT sequence when inserting data to a table cleared by using delete, and continue to use it to number the AUTOINCREMENT field. After truncate deletes a table, the table starts from 1 and is the autoincrement Field number.

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.

 

Difference between truncate table and delete in SQL
The truncate table function is the same as the Delete statement without the Where clause: Both Delete all rows in the TABLE. However, truncate table is faster than Delete and uses less system and transaction log resources.

The Delete statement deletes a row at a time and records one row in the transaction log. Truncate table deletes data by releasing the data pages used to store TABLE data, and only records the release of pages in transaction logs.

Truncate table deletes all rows in the TABLE, but the TABLE structure, its columns, constraints, and indexes remain unchanged. The count value used by the new row ID is reset to the seed of the column. To retain the ID count value, use Delete instead.

For tables referenced by the foreign key constraint, the truncate table cannot be used, but the Delete statement without the Where clause should be used. Because the truncate table is not recorded in the log, it cannot activate the trigger.

The truncate table cannot be used in the index View.

Similarities and differences between truncate, delete, and drop:
Note: The delete statement is a delete statement without the where clause.
 
Similarities: truncate, delete without where clause, and drop both delete table data.

Differences:
1. truncate and delete: delete only data. Do not delete the table structure (definition)
The drop statement will delete the constraints, triggers, and indexes that the table structure is dependent on. Stored Procedures/functions that depend on the table will be retained, but it changes to the invalid status.
 
2. The delete statement is dml, which is put into the rollback segement and takes effect only after the transaction is committed. If a trigger exists, it is triggered during execution.
Truncate, drop is ddl, and the operation takes effect immediately. The original data is not stored in rollback segment, and cannot be rolled back. trigger is not triggered.

3. The delete statement does not affect the extent used by the table. The high w2atermark (high w2atermark) keeps the original position unchanged.
Obviously, the drop statement releases all the space occupied by the table.
By default, the truncate statement releases the space to the extent of minextents, unless reuse storage is used; truncate resets the high water line (back to the beginning ).

4. Speed. Generally, drop> truncate> delete

5. Security: be careful when using drop and truncate, especially when there is no backup. Otherwise, it will be too late to cry.

Usage:
To delete some data rows, use delete. Note that the where clause should be included. The rollback segment should be large enough.

To delete a table, use drop

Delete all data if you want to keep the table. If it is not related to the transaction, use truncate. If it is related to the transaction or you want to trigger the trigger, use delete.

If you want to organize fragments in the table, you can use truncate to keep up with the reuse stroage, and then re-import/insert data.