mysql Two Ways of Emptying Table Data and Difference Analysis

There are two ways to delete data in MySQL:

truncate (truncated) is a rough type of emptying delete is a refined deletion

Delete operation

If you need to empty all the data in the table, either of the following can be done:

delete from tablename;
truncate table tablename;

If you only delete 1 part of the data, you can only use delete:

delete from tablename where case1 and case2;

Difference

Only delete can be used when deleting some data finely.
When clearing all table data, both can be used. At this time, there is a definite difference between the two methods:

Return value

truncate returns a value of 0, while delete returns the number of deleted records

mysql> truncate serviceHost;
Query OK, 0 rows affected (0.04 sec)
mysql> delete from serviceHost where creator='test';
Query OK, 4 rows affected (0.01 sec)

Self-increasing field

If there is a self-increasing field in the table, truncate is reset to 1, while delete keeps the self-increasing maximum.

Executive efficiency

truncate does not scan the table, which is equivalent to recreating the table, only retaining the structure of the table, and then deleting the original table, which is very efficient.
delete scans the whole table and makes judgments based on where statements, so it is inefficient.

Operation log

truncate does not write to the server log and cannot recover.
delete writes the server log.

Trigger

truncate does not activate triggers; delete does.

Summarize