Two ways to clear table data in MySQL and their differences

There are two ways to delete data in MySQL:

1. Truncate is a rough type of clearing
2. delete is a refined deletion

Delete Operation

If you need to clear all the data in the table, you can do either of the following:

delete from tablename;
truncate table tablename;

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

delete from tablename where case1 and case2;

the difference

When deleting some data in a granular manner, only delete can be used.
When clearing all table data, both methods are acceptable. There are some differences between the two methods:

Return Value

The return value of truncate is 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)

Auto-increment field

If there is an auto-increment field in the table, truncate will reset it to 1, while delete will keep the maximum auto-increment value.

Execution efficiency

Truncate does not scan the table, which is equivalent to recreating the table, retaining only the table structure, and then deleting the original table, which is very efficient.
Delete will scan the entire table and make judgments based on the where statement, so it is inefficient.

Operation log

Truncate does not write to the server log and cannot be recovered.
delete will write to the server log.

trigger

Truncate does not activate the trigger, but delete does.

Summarize

The above is the two ways to clear MySQL table data and their differences that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!