MySQL Batch Delete: Remove Millions of Records

In MySQL, you can use the DELETE statement along with the LIMIT keyword to delete a large amount of data in batches, in order to avoid performance issues or database locking that may result from deleting all data at once.

For example, to delete the first 10 million records from a table, you can use the following statement:

DELETE FROM table_name LIMIT 10000000;

This will remove the first 10 million records from the table. If you need to delete more data, you can execute the same statement multiple times, each time deleting a certain amount of data until all the data is removed.

Another option to consider is using the TRUNCATE TABLE statement to delete all data from the table and then reinsert the data that needs to be retained, which is usually more efficient than deleting data one by one. However, it is important to note that the TRUNCATE TABLE statement will delete all data in the table, so use it carefully.