mysql advanced (fifteen) mysql batch delete large amounts of data

mysqlDelete large amounts of data in batches

Suppose there is a table(syslogs)with1000 Ten thousand records, it is necessary to delete all the records with statusid=1 without stopping the business. There are almost 600 , directly execute DELETE FROM syslogs WHERE statusid=1 and you will find that the deletion failed because of the lock wait timeout exceeded error.

Because this statement involves too many records, we delete them in batches through the LIMIT parameter, such as every 10000 items are deleted once, then we can use statements like MySQL to complete :

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

and then execute it multiple times. These records were deleted successfully.

Note:

Be careful to use limit when performing large batch deletion. Because if limit is not used, deleting a large amount of data is likely to cause a deadlock.

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

It is best to add limit 1 # when update and delete ## to prevent misuse.

The above is the content of mysql advanced, batch deletion, and large amounts of data. For more related content, please pay attention to the PHP Chinese website (www.php.cn )!