Recipe 10.7. Handling Duplicate Key Values

Problem

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

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 row 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 rows or to replace old rows with the new ones.

Suppose that 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,   PRIMARY KEY (station, type) ); 

To make sure that you have only one row for each station for each type of measurement, the table includes a primary 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;             

mysqlimport has --ignore and --replace options that have the same effect as the IGNORE and REPLACE keywords for LOAD DATA.