Home > Database > Mysql Tutorial > A brief discussion on the two methods and differences of clearing table data in mysql

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

青灯夜游
Release: 2019-11-27 17:43:48
forward
2828 people have browsed it

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;
Copy after login

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

delete from tablename where case1 and case2;
Copy after login

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)
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Issues
MySQL stops process
From 1970-01-01 08:00:00
0
0
0
Error when installing mysql on linux
From 1970-01-01 08:00:00
0
0
0
phpstudy cannot start mysql?
From 1970-01-01 08:00:00
0
0
0
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template