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
Table of contents Preface Introduction to Dockerf...
Detailed explanation of MySQL exporting data from...
1. Use CSS Copy code The code is as follows: style...
This article example shares the specific code for...
Recently, the client of a project insisted on hav...
1. Execute the select statement first to generate...
Using the knowledge of CSS variables, I will dire...
Git is integrated in vscode, and many operations ...
Table of contents 1. Pull the image 2. Create a R...
Table of contents 1. Instructions for use 2. Prep...
Preface In many MySQL test scenarios, some test d...
For individual webmasters, how to make their websi...
Vue+js realizes the fade in and fade out of the v...
There are two ways to delete data in MySQL, one i...