Shulou(Shulou.com)06/01 Report--

How does mysql delete the data in the database in batch? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Mysql batch deletion method of data in the database: delete in batches through the LIMIT parameter, the syntax is [DELETE FROM syslogs WHERE status=1 ORDER BY statusid LIMIT 10000].

Mysql method to delete data in a batch from a database:

Suppose a table (syslogs) has 10 million records, and you need to delete all the records in statusid=1 without stopping the business. There are almost 6 million records. If you execute DELETE FROM syslogs WHERE statusid=1 directly, you will find that the deletion failed because of the error of lock wait timeout exceed.

Because there are too many records involved in this statement, we delete it in batches by using the LIMIT parameter, such as every 10000 entries, so we can use statements like MySQL to do this:

DELETE FROM syslogs WHERE status=1 ORDER BY statusid LIMIT 10000

These records can then be deleted successfully by executing them multiple times.

Note:

Be careful to use limit when performing mass deletions. Because if you don't use limit, deleting a large amount of data is likely to cause deadlocks.

If the where statement of delete is not on the index, you can find the primary key first, and then delete the database based on the primary key.

It is best to add limit 1 to update and delete to prevent misoperation.

Thank you for reading! After reading the above, do you have a general idea of how mysql deletes the data in the database in batches? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.