Tuesday, October 27, 2009

10.8 Handling Duplicate Index Values




I l@ve RuBoard










10.8 Handling Duplicate Index Values




10.8.1 Problem



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





10.8.2 Solution



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





10.8.3 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;









    I l@ve RuBoard



    No comments: