Database Reference
In-Depth Information
We have a few choices of how to handle such an error. We could modify the table so that
the prospect_email column allows for duplicate email addresses. Another choice
would be to tell MySQL to ignore any errors like this. To do this, we wouldadd the
IGNORE option to the LOAD DATA INFILE statement. Try entering this:
LOAD DATA INFILE ' /tmp/birdwatcher-prospects.csv '
IGNORE INTO TABLE birdwatchers.birdwatcher_prospects_import
FIELDS TERMINATED BY '|' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '[' TERMINATED BY ']\r\n'
IGNORE 1 LINES
(prospect_name, prospect_email, prospect_country);
Query OK, 4 rows affected, 1 warning (0.02 sec)
Records: 5 Deleted: 0 Skipped: 1 Warnings: 1
SHOW WARNINGS \G
*************************** 1. row ***************************
Level: Warning
Code: 1062
Message: Duplicate entry 'bettasveta@gmail.com' for key
'prospect_email'
This worked. Notice the results message. It says one row was skipped and there's a warn-
ing. The warning in turn says there's a duplicate entry. That's the row it skipped, the
second entry for Sveta. Let's execute a SELECT statement to see how the row for Sveta
looks now:
SELECT * FROM birdwatchers.birdwatcher_prospects_import
WHERE prospect_name LIKE '%Sveta%' \G
*************************** 1. row ***************************
prospect_id: 16
prospect_name: Ms. Sveta Smirnova
prospect_email: bettasveta@gmail.com
prospect_country: Russia
This shows that the first record for Sveta was inserted into the table, but the second one
was not. We know this because the second record included a middle initial for her name.
If we prefer that duplicate records replace previous ones, we can replacethe IGNORE op-
tion with the REPLACE option. The statement would then be entered as follows:
LOAD DATA INFILE ' /tmp/birdwatcher-prospects.csv '
REPLACE INTO TABLE birdwatchers.birdwatcher_prospects_import
FIELDS TERMINATED BY '|' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '[' TERMINATED BY ']\n'
Search WWH ::




Custom Search