Home >Database >Mysql Tutorial >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 < maxId limit 10000". Can.
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 < '2017-04-06'
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!