Massive MySQL delete from CSV file

Question

To insert massive CSV file into MySQL, there is LOAD DATA INFILE, but is there any similar command to delete massive entries with keys stored in a massive CSV file?

If not, what would you see as the fastest way to perform such operation?

EDIT: Also which one of these 2 methods would you see as the most efficient:

Multiple deletes

DELETE FROM myTable WHERE mykey=1 LIMIT 1;
DELETE FROM myTable WHERE mykey=2 LIMIT 1;
...
DELETE FROM myTable WHERE mykey=300000 LIMIT 1;

Or Batch delete

DELETE FROM myTable WHERE mykey IN (1,2,...,10000);
DELETE FROM myTable WHERE mykey IN (10001,10002,...,20000);
...
DELETE FROM myTable WHERE mykey IN (...,...,300000);

Thanks!

Answer 1

DELETE TECHNIQUE #1

If you have a massive delete of keys, you may want to resort to the opposite of doing a massive DELETE: Create the table with the remaining values.

Let's say you loaded a table from the CSV file called KeysToDelete with the field mykey

Here is how to perform the massive DELETE without using DELETE

CREATE TABLE mynewtable FROM mytable;
INSERT INTO mynewtable
SELECT A.* FROM mytable A
LEFT JOIN KeysToDelete B USING (mykey)
WHERE B.mykey IS NULL;
ALTER TABLE mytable RENAME myoldtable;
ALTER TABLE mynewtable RENAME mytable;

That's it.

DELETE TECHNIQUE #2

If you want to use DELETE and the table is not that big, you could do a DELETE JOIN as follows:

DELETE A.* FROM mytable A INNER JOIN KeysToDelete B USING (mykey);

Here are my earlier posts where I discuss these techniques:

• Feb 15, 2012 : How to implement soft deletes?
• Mar 05, 2012 : Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios
• Nov 07, 2012 : Can I do anything about a very long running KILL command?
• Aug 07, 2013 : How to delete duplicate records

Give it a Try !!!

Answer 2

I don't think there is a similar command, but if your keys are simple (not composed) you can do the trick.

I usually generate a DELETE script with an SELECT CONCAT statement

You have your "IDs" file :

root@test:/tmp# cat /tmp/id.out
1
2
34
56
2345
3
4

Create a temp table to store it in SQL:

CREATE TEMPORARY TABLE test.ids (id int PRIMARY KEY);

Load your IDs:

LOAD DATA INFILE '/tmp/id.out' INTO TABLE test.ids;

Then generate a delete.sql script with a SELECT CONCAT:

SELEC CONCAT("DELETE FROM myTable WHERE ID=", id," LIMIT 1;") FROM test.ids INTO OUTFILE "/tmp/delete.sql";

Now you have your delete script:

root@test:/tmp# cat "/tmp/delete.sql"
DELETE FROM myTable WHERE ID=1 LIMIT 1;
DELETE FROM myTable WHERE ID=2 LIMIT 1;
DELETE FROM myTable WHERE ID=3 LIMIT 1;
DELETE FROM myTable WHERE ID=4 LIMIT 1;
DELETE FROM myTable WHERE ID=34 LIMIT 1;
DELETE FROM myTable WHERE ID=56 LIMIT 1;
DELETE FROM myTable WHERE ID=2345 LIMIT 1;

Then you can run your DELETE scripts:

mysql> source /tmp/delete.sql
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

You can also generate this type of script with OS tools (awk or notepad++)

You can run this type of script safely on a production master, you DELETE rows from the PK and you limit with LIMIT 1 so you do not risk to have replication lags.

Best Regards.

Max.