MySQL Cookbook

Handling Duplicate Index Values

Problem

Your input contains records that duplicate the values of unique keys in existing table records.

Solution

Tell LOAD DATA to ignore the new records, or to replace the old ones.

Discussion

By default, an error occurs if you attempt to load a record that duplicates an existing record in the column or columns that form a PRIMARY KEY or UNIQUE index. To control this behavior, specify IGNORE or REPLACE after the filename to tell MySQL to either ignore duplicate records or to replace old records with the new ones.

Suppose you periodically receive meteorological data about current weather conditions from various monitoring stations, and that you store measurements of various types from these stations in a table that looks like this:

CREATE TABLE weatherdata
(
    station INT UNSIGNED NOT NULL,
    type    ENUM('precip','temp','cloudiness','humidity','barometer') NOT NULL,
    value   FLOAT,
    UNIQUE (station, type)
);

To make sure that you have only one record for each station for each type of measurement, the table includes a unique key on the combination of station ID and measurement type. The table is intended to hold only current conditions, so when new measurements for a given station are loaded into the table, they should kick out the station’s previous measurements. To accomplish this, use the REPLACE keyword:

mysql> LOAD DATA LOCAL INFILE 'data.txt' REPLACE INTO TABLE weatherdata;