mysql batch delete large amounts of data

mysql batch delete large amounts of data

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:
  • Code for batch deleting data tables with the same prefix in Mysql in PHP

<<:  Vue2.x - Example of using anti-shake and throttling

>>:  How to set Tomcat as an automatically started service? The quickest way

Recommend

Implementation of building custom images with Dockerfile

Table of contents Preface Introduction to Dockerf...

Several ways to hide Html elements

1. Use CSS Copy code The code is as follows: style...

JavaScript implements an input box component

This article example shares the specific code for...

MySQL uses the truncate command to quickly clear all tables in a database

1. Execute the select statement first to generate...

Sample code for CSS dynamic loading bar effect

Using the knowledge of CSS variables, I will dire...

VSCode configuration Git method steps

Git is integrated in vscode, and many operations ...

Building a Redis cluster on Docker

Table of contents 1. Pull the image 2. Create a R...

Detailed explanation of MYSQL stored procedure comments

Table of contents 1. Instructions for use 2. Prep...

...

How to batch generate MySQL non-duplicate mobile phone number table example code

Preface In many MySQL test scenarios, some test d...

Website background music implementation method

For individual webmasters, how to make their websi...

Vue+js realizes video fade-in and fade-out effect

Vue+js realizes the fade in and fade out of the v...

How to delete table data in MySQL

There are two ways to delete data in MySQL, one i...