How to Simulate ON DUPLICATE KEY UPDATE with MySQL's LOAD DATA INFILE?

Emulating ON DUPLICATE KEY UPDATE with MySQL LOAD DATA INFILE

Despite its speed advantage for loading large datasets into MySQL, LOAD DATA INFILE lacks native support for the ON DUPLICATE KEY UPDATE functionality. This limitation poses challenges when updating existing records without replacing auto-increment IDs or incurring unnecessary overhead.

Solution:

To emulate ON DUPLICATE KEY UPDATE using LOAD DATA INFILE, you can follow these steps:

1. Create a Temporary Table: Construct a temporary table with the same structure as the target table using CREATE TEMPORARY TABLE.
2. Optionally Optimize: To expedite the process, disable indices on the temporary table by executing DROP INDEX commands for any existing indices.
3. Load Data into Temporary Table: Utilize LOAD DATA INFILE to import the CSV data into the temporary table, specifying appropriate field delimiters and enclosures.
4. Transfer Data with ON DUPLICATE KEY UPDATE: Employ an INSERT statement with ON DUPLICATE KEY UPDATE to copy data from the temporary table to the target table. Ensure to match the column names correctly.
5. Delete Temporary Table: Release the temporary table by executing DROP TEMPORARY TABLE.

This approach leverages the speed of LOAD DATA INFILE while emulating the functionality of ON DUPLICATE KEY UPDATE. By automating the process with SHOW INDEX FROM and SHOW COLUMNS FROM, you can easily adapt it to any given table.

The above is the detailed content of How to Simulate ON DUPLICATE KEY UPDATE with MySQL's LOAD DATA INFILE?. For more information, please follow other related articles on the PHP Chinese website!