How to delete data in batches in mysql

The method of batch deleting data in mysql: first find the maximum id that meets the conditions; then create an index on the where field; finally delete the data in batches through the command "delete from table_name where id

Recommended: "mysql video tutorial"

mysql batch deletion of big data

Mysql deleting a large amount of data at one time may cause deadlock, and may also throw an exception

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

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

lock wait timeout exceed timeout

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

A better method on the Internet is:

First find out the largest id that meets the conditions (the ids here are ordered and do not need to be consecutive)

select max(id) from table_name where create_time < &#39;2017-04-06&#39;

Creating an index on the where field can improve efficiency

about ten million The data takes about 10 seconds

Then delete it by ID, delete 10k at a time, and delete it in a loop

delete from table_name where id < maxId limit 10000

The above is the detailed content of How to delete data in batches in mysql. For more information, please follow other related articles on the PHP Chinese website!