Shulou(Shulou.com)05/31 Report--

This article will explain in detail the methods of clearing table data in mysql. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

There are two ways to delete data in MySQL:

Truncate (truncated) belongs to rough emptying.

Delete is a refined deletion.

Delete operation

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

Delete from tablename;truncate table tablename

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

Delete from tablename where case1 and case2

Difference

Only delete can be used when finely deleting part of the data.

When emptying all table data, both can be done, and there is a 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)

Auto-increment field

If there are self-incrementing fields in the table, truncate resets to 1, while delete maintains the maximum value of self-increment.

Execution efficiency

Truncate does not scan the table, which is equivalent to recreating the table, retaining only 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 server logs and cannot recover.

Delete writes server logs.

Trigger

Truncate does not activate triggers, delete activates triggers.

This is the end of this article on "what are the methods of mysql emptying table data". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.