Obtaining Diagnostics About Bad Input Data
Problem
When you
issue a LOAD
DATA
statement, you want to know whether any
input values are bad and what’s wrong with them.
Solution
Use the information line displayed by LOAD
DATA
to determine whether there are any problematic input values. If so,
use
SHOW
WARNINGS
to find where they are and what the
problems are.
Discussion
When a LOAD
DATA
statement finishes, it returns a line
of information that tells you how many errors or data conversion
problems occurred. Suppose that you load a file into a table and see
the following message when LOAD
DATA
finishes:
Records: 134 Deleted: 0 Skipped: 2 Warnings: 13
These values provide some general information about the import operation:
Records
indicates the number of records found in the file.Deleted
andSkipped
are related to treatment of input records that duplicate existing table rows on unique index values.Deleted
indicates how many rows were deleted from the table and replaced by input records, andSkipped
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 stringabc
into a numeric column results in a stored value of0
, for example.)
What do these values tell you? The Records
value normally ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.