Importing and Exporting Data - MySQL - page 372

LOAD DATA provides options to address many of the import issues mentioned in the
chapter introduction, such as the line-ending sequence for recognizing how to break
input into records, the column value delimiter that permits records to be broken into
separate values, the quoting character that may enclose column values, quoting and
escaping conventions within values, and NULL value representation:
• By default, LOAD DATA expects the datafile to have the same number of columns as
the table into which you load it, with the columns present in the same order as in
the table. If the file column number or order differ from the table, you can specify
which columns are present and their order. If the datafile contains fewer columns
than the table, MySQL assigns default values for the missing columns.
• LOAD DATA assumes that data values are separated by tab characters and that lines
end with linefeeds (newlines). If a file doesn't conform to these conventions, you
can specify its format explicitly.
• You can indicate that data values may have quotes around them that should be
stripped, and you can specify the quote character.
• Several special escape sequences are recognized and converted during input pro‐
cessing. The default escape character is backslash ( \ ), but you can change it. The
\N sequence is interpreted as a NULL value. The \b , \n , \r , \t , \\ , and \0 sequences
are interpreted as backspace, linefeed, carriage return, tab, backslash, and ASCII
NUL characters. (NUL is a zero-valued byte; it differs from the SQL NULL value.)
• LOAD DATA provides diagnostic information about which input values cause prob‐
lems. To display this information, execute a SHOW WARNINGS statement after the LOAD
DATA statement.
The remainder of this section describes how to handle these issues using LOAD DATA or
mysqlimport . It's lengthy because there's a lot to cover.
Specifying the datafile location
You can load files located either on the server host, or on the client host from which you
issue the LOAD DATA statement. Telling MySQL where to find your datafile is a matter of
knowing the rules that determine where it looks for the file (particularly important for
files not in your current directory).
By default, the MySQL server assumes that the datafile is located on the server host. You
can load local files that are located on the client host using LOAD DATA LOCAL rather than
LOAD DATA , unless LOCAL capability is disabled by default. You might be able to enable it
using the --local-infile option for mysql . If that doesn't work, your server has been
configured to prohibit LOAD DATA LOCAL .