How Can I Mimic ON DUPLICATE KEY UPDATE Using LOAD DATA INFILE in MySQL?

Emulating ON DUPLICATE KEY UPDATE with LOAD DATA INFILE in MySQL

LOAD DATA INFILE in MySQL is a powerful tool for rapidly importing large datasets. However, it lacks support for the ON DUPLICATE KEY UPDATE clause, which can lead to data inconsistencies or performance challenges. To circumvent this limitation, an alternative approach is required.

To emulate ON DUPLICATE KEY UPDATE functionality, consider the following steps:

1. Create a Temporary Table:

CREATE TEMPORARY TABLE temporary_table LIKE target_table;

2. Optimize Temporary Table (Optional):

Speed up data loading by removing indices from the temporary table:

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

3. Load CSV into Temporary Table:

Perform a basic data import using LOAD DATA INFILE:

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

4. Copy Data with ON DUPLICATE KEY UPDATE:

Update the target table while preserving existing records:

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 Temporary Table:

Once the data transfer is complete, discard the temporary table:

DROP TEMPORARY TABLE temporary_table;

By following these steps, you can emulate ON DUPLICATE KEY UPDATE functionality with LOAD DATA INFILE, optimizing data loading while maintaining data integrity.

The above is the detailed content of How Can I Mimic ON DUPLICATE KEY UPDATE Using LOAD DATA INFILE in MySQL?. For more information, please follow other related articles on the PHP Chinese website!