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

How to deploy SpringBoot project using Dockerfile

1. Create a SpringBooot project and package it in...

Detailed explanation of CSS3 animation and new features of HTML5

1. CSS3 animation ☺CSS3 animations are much easie...

HTML basic syntax is convenient for those who are just starting to learn HTML

1.1 General marking A general tag consists of an ...

Thoroughly understand JavaScript prototype and prototype chain

Table of contents Preface Laying the foundation p...

MySQL database transaction example tutorial

Table of contents 1. What is a transaction? 2. Th...

Detailed explanation of the difference between Vue life cycle

Life cycle classification Each component of vue i...

Reasons and solutions for not being able to detect array changes in Vue2

Table of contents Workaround Why can't I moni...

A brief discussion on the corresponding versions of node node-sass sass-loader

Table of contents The node version does not corre...

Implementation of pushing Docker images to Docker Hub

After the image is built successfully, it can be ...

WeChat applet selects the image control

This article example shares the specific code for...

Common front-end JavaScript method encapsulation

Table of contents 1. Enter a value and return its...

Detailed explanation of Docker Swarm concepts and usage

Docker Swarm is a container cluster management se...

MySQL 5.7.19 installation and configuration method graphic tutorial (win10)

Detailed tutorial on downloading and installing M...