The method of deleting data in batch in mysql

How to delete data in batch in mysql? 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!

The method of bulk deleting data in mysql: first, find out the maximum id; that meets the condition, and then build an index on the where field; finally, delete the data in batches by using the command "delete from table_name where id < maxId limit 10000".

Batch deletion of big data by mysql

It is possible for mysql to delete a large amount of data at once, and it may also cause deadlock and throw an exception.

The total number of locks exceeds the lock table size in MySQL

This is because the data to be deleted is too large, and the buffer given by mysql seems to be only about 8MB.

Lock wait timeout exceed timeout

Therefore, it needs to be processed in batches, which is also a common idea when operating big data.

One of the better ways to do this online is:

First of all, find out the maximum id that meets the condition (here the id is ordered and does not need to be continuous)

Select max (id) from table_name where create_time < '2017-04-06'

Indexing on the where field can improve efficiency

It takes about 10 seconds for tens of millions of data.

Then press id to delete, delete 10k at a time, and delete in a loop

Delete from table_name where id < maxId limit 10000 Thank you for reading! After reading the above, do you have a general idea of how to delete data in bulk in mysql? 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.