Importing and Exporting Data - MySQL - page 378

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 meas‐
urements. 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 correspond to the IGNORE and
REPLACE keywords for LOAD DATA .
Obtaining diagnostics about bad input data
LOAD DATA displays an information line to indicate whether there are any problematic
input values. If so, use SHOW WARNINGS to find where they are and what the problems are.
When a LOAD DATA statement finishes, it returns a line of information that tells you how
many errors or data conversion problems occurred. For example:
Records: 134 Deleted: 0 Skipped: 2 Warnings: 13
These values provide general information about the import operation:
• Records indicates the number of records found in the file.
• Deleted and Skipped are related to treatment of input records that duplicate ex‐
isting table rows on unique index values. Deleted indicates how many rows were
deleted from the table and replaced by input records, and Skipped indicates how
many input records were ignored in favor of existing rows.
• Warnings is something of a catchall that indicates the number of problems found
while loading data values into columns. Either a value stores into a column properly,
or it doesn't. In the latter case, the value ends up in MySQL as something different,
and MySQL counts it as a warning. (Storing a string abc into a numeric column
results in a stored value of 0 , for example.)
What do these values tell you? The Records value normally should match the number
of lines in the input file. If it doesn't, that's a sign that MySQL interprets the file as having
a different format than it actually has. In this case, you'll likely also see a high Warn
ings value, which indicates that many values had to be converted because they didn't
match the expected data type. The solution to this problem often is to specify the proper
FIELDS and LINES clauses.
Assuming that your FIELDS and LINES format specifiers are correct, a nonzero Warn
ings count indicates the presence of bad input values. You can't tell from the numbers
in the LOAD DATA information line which input records had problems or which columns
were bad. To get that information, issue a SHOW WARNINGS statement.
Suppose that a table t has this structure:
CREATE TABLE t
(