MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE

load-data-infileMySQLon-duplicate-key

For loading huge amounts of data into MySQL, LOAD DATA INFILE is by far the fastest option. Unfortunately, while this can be used in a way INSERT IGNORE or REPLACE works, ON DUPLICATE KEY UPDATE is not currently supported.

However, ON DUPLICATE KEY UPDATE has advantages over REPLACE. The latter does a delete and an insert when a duplicate exists. This brings overhead for key management. Also, autoincrement ids will not stay the same on a replace.

How can ON DUPLICATE KEY UPDATE be emulated when using LOAD DATA INFILE?

Best Answer

These steps can be used to emulate this functionality:

1) Create a new temporary table.

CREATE TEMPORARY TABLE temporary_table LIKE target_table;

2) Optionally, drop all indices from the temporary table to speed things up.

SHOW INDEX FROM temporary_table;
DROP INDEX `PRIMARY` ON temporary_table;
DROP INDEX `some_other_index` ON temporary_table;

3) Load the CSV into the temporary table

LOAD DATA INFILE 'your_file.csv'
INTO TABLE temporary_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(field1, field2);

4) Copy the data using ON DUPLICATE KEY UPDATE

SHOW COLUMNS FROM target_table;
INSERT INTO target_table
SELECT * FROM temporary_table
ON DUPLICATE KEY UPDATE field1 = VALUES(field1), field2 = VALUES(field2);

5) Remove the temporary table

DROP TEMPORARY TABLE temporary_table;

Using SHOW INDEX FROM and SHOW COLUMNS FROM this process can be automated for any given table.

Related Topic