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

Emulating MySQL's ON DUPLICATE KEY UPDATE with LOAD DATA INFILE

LOAD DATA INFILE remains the predominant approach for rapidly importing substantial datasets into MySQL. Its effectiveness may fall short when seeking to implement behaviors analogous to INSERT IGNORE or REPLACE, as ON DUPLICATE KEY UPDATE support is absent. Nevertheless, ON DUPLICATE KEY UPDATE offers benefits over REPLACE, avoiding performance penalties associated with deletions and insertions upon encountering duplicates, as well as preserving autoincrement IDs.

To emulate ON DUPLICATE KEY UPDATE functionality during LOAD DATA INFILE operations, follow these steps:

1. Establish a Temporary Table: Create a temporary table termed 'temporary_table' sharing the same structure as the target table 'target_table'.
2. Optional: Disable Indices: To optimize performance, eliminate indices from the temporary table using commands like 'DROP INDEX' against indices 'PRIMARY' and 'some_other_index'.
3. Load Data into Temporary Table: Execute the LOAD DATA INFILE command to load the CSV file into the temporary table.
4. Copy Data with ON DUPLICATE KEY UPDATE: Use an INSERT statement that includes the ON DUPLICATE KEY UPDATE clause to copy data from the temporary table to the target table, updating duplicate key values.
5. Remove Temporary Table: Once the data transfer is complete, delete the temporary table.

Employing the commands 'SHOW INDEX FROM' and 'SHOW COLUMNS FROM' enables the automation of this process for any specified table.

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