mysql batch delete large amounts of data Assume that there is a table (syslogs) with 10 million records. You need to delete all the records with statusid=1 without stopping the business. There are about 6 million records. If you directly execute DELETE FROM syslogs WHERE statusid=1, you will find that the deletion fails because of the lock wait timeout exceed error. Because this statement involves too many records, we delete them in batches using the LIMIT parameter, for example, deleting every 10,000 records. MySQL can be completed with the following statement: DELETE FROM syslogs WHERE status=1 ORDER BY statusid LIMIT 10000; Then you can successfully delete these records by executing it multiple times. Note: When performing large-scale deletions, be sure to use limit. Because if you don't use limit, deleting a large amount of data is likely to cause deadlock. If the where clause 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 updating and deleting to prevent accidental operations. Thank you for reading, I hope it can help you, thank you for your support of this site! You may also be interested in:
|
<<: Vue2.x - Example of using anti-shake and throttling
>>: How to set Tomcat as an automatically started service? The quickest way
1. Create a SpringBooot project and package it in...
1. CSS3 animation ☺CSS3 animations are much easie...
1.1 General marking A general tag consists of an ...
Table of contents Preface Laying the foundation p...
Table of contents 1. What is a transaction? 2. Th...
Life cycle classification Each component of vue i...
Table of contents Workaround Why can't I moni...
a href="#"> After clicking the link, ...
Table of contents The node version does not corre...
After the image is built successfully, it can be ...
This article example shares the specific code for...
This article uses examples to illustrate the prin...
Table of contents 1. Enter a value and return its...
Docker Swarm is a container cluster management se...
Detailed tutorial on downloading and installing M...