A brief discussion on the two methods and differences of clearing table data in mysql

There are two ways to delete data in MySQL:

1, truncate (truncation) is a rough type Clear

2 and delete are refined deletions

Delete operations

If you You need to clear all the data in the table, either of the following two methods are available:

delete from tablename;
truncate table tablename;

And if you only delete a part of the data, you can only use delete:

delete from tablename where case1 and case2;

Difference

When deleting part of the data in a granular manner, you can only use delete.

When clearing all table data, both methods can be used. At this time, there are certain differences between the two methods:

1. Return value

truncateThe return value is 0, while delete will return 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)

2. Auto-increment field

If there are auto-increment fields in the table, truncate will be reset to 1, and delete will maintain the maximum auto-increment value .

3. Execution efficiency

truncateNot scanning the table is equivalent to re-creating the table, only retaining the structure of the table, and then deleting it. The original table is very efficient.
delete will scan the entire table and make judgments based on the where statement, so the efficiency is low.

4. Operation log

truncateIf the server log is not written, it cannot be restored.
delete will write server logs.

5. Trigger

truncate does not activate the trigger, delete will activate the trigger.

Recommended learning: MySQL tutorial

The above is the detailed content of A brief discussion on the two methods and differences of clearing table data in mysql. For more information, please follow other related articles on the PHP Chinese website!