MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE

Question

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?

Variations on this are discussed in stackoverflow.com/questions/73693737/… — Rick James
– Rick James, Commented Sep 14, 2022 at 14:57

Giacomo1968 · Accepted Answer · 2021-11-23 19:20:17Z

109

These steps can be used to emulate this functionality:

Create a new temporary table.

CREATE TEMPORARY TABLE temporary_table LIKE target_table;

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;

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);

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);

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.

edited Nov 23, 2021 at 19:20

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

answered Mar 7, 2013 at 12:25

Jan

3,1543 gold badges25 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

JonoCoetzee Over a year ago

I suggest rather using INSERT INTO target_table SELECT coloumn_name1, coloumn_name1 FROM temporary_table because * will include the primary key and cause items with the same primary key (in the case of an auto_increment primary key) in the main table to be updated, otherwise this worked for me!

Chubas Over a year ago

Thanks for the answer, worked for me after a little of SQL juggling. My SQL-fu is very rusty, nice to learn neat techniques like this.

Shaun Over a year ago

Thanks from here too - works brilliantly - didn't think it was possible to do a ON DUPLICATE KEY with LOAD DATA INFILE, so never tried before.

imVJ Over a year ago

@verisimilitude - Dropping indexes will improve performance for loading data from file!

guigoz Over a year ago

Steps 1) and 2) can be done in a single statement: CREATE TEMPORARY TABLE temporary_table SELECT * FROM target_table LIMIT 0;

|

Giacomo1968 · Accepted Answer · 2021-11-23 19:16:41Z

5

We can replace first (two steps) with below single query in the answer shared by Jan.

For steps 1 and 2 we can create new table with same reference structure and without any indexes.

CREATE TEMPORARY TABLE temporary_table SELECT * FROM target_table WHERE 1=0;

Instead of.

Create a new temporary table.

CREATE TEMPORARY TABLE temporary_table LIKE target_table;

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;

edited Nov 23, 2021 at 19:16

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

answered Sep 6, 2017 at 10:33

Suneet Khurana

4495 silver badges11 bronze badges

Comments

tecdoc ukr net · Accepted Answer · 2022-03-16 10:49:14Z

Non-LOCAL Versus LOCAL Operation

The LOCAL modifier affects these aspects of LOAD DATA, compared to non-LOCAL operation:

It changes the expected location of the input file; see Input File Location.
It changes the statement security requirements; see Security Requirements.
It has the same effect as the IGNORE modifier on the interpretation of input file contents and error handling; see Duplicate-Key and Error Handling, and Column Value Assignment.

LOCAL works only if the server and your client both have been configured to permit it. For example, if mysqld was started with the local_infile system variable disabled, LOCAL produces an error. See Section 6.1.6, “Security Considerations for LOAD DATA LOCAL”.

Collectives™ on Stack Overflow

MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE

3 Answers 3

13 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related