How to batch delete data in the database in mysql

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

Mysql method of batch deleting data in the database:

Suppose there is a table (syslogs) with 10 million entries Records, it is necessary to delete all records with statusid=1 without stopping the business. There are almost 6 million records. Directly execute DELETE FROM syslogs WHERE statusid=1 and you will find that the deletion fails because lock wait timeout exceed mistake.

Because this statement involves too many records, we delete it in batches through the LIMIT parameter. For example, delete every 10,000 records. Then we can use a statement like MySQL to Complete:

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

Then execute it multiple times to successfully delete these records.

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 deadlock.

• If the delete where statement 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 updating and deleting to prevent misuse.

More related free learning recommendations: mysql tutorial(Video)

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