MySQL provides a LOAD
DATA
statement that acts as a bulk
data loader. Here’s an example statement that reads a file mytbl.txt from your current directory and
loads it into the table mytbl
in
the default database:
mysql>LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl;
At some MySQL installations, the LOCAL
loading capability may have been
disabled for security reasons. If that is true at your site, omit
LOCAL
from the statement and
specify the full pathname to the file. See Specifying the Datafile Location for more information on local
versus non-local data loading.
MySQL also includes a utility program named mysqlimport that acts as a wrapper around
LOAD
DATA
so that you can load input files
directly from the command line. The mysqlimport command that is equivalent to
the preceding LOAD
DATA
statement looks like this, assuming
that mytbl
is in the cookbook
database:
%mysqlimport --local cookbook mytbl.txt
For mysqlimport, as with
other MySQL programs, you may need to specify connection parameter
options such as --user
or --host
(Starting and Stopping mysql).
The following list describes LOAD
DATA
’s general characteristics and
capabilities; mysqlimport shares most of these
behaviors. There are some differences that we’ll note as we go along,
but for the most part you can interpret references to LOAD
DATA
as references to mysqlimport as
well.
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 allows 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 contain the
same number of columns as the table into which you’re loading
data, and the datafile columns must be present in the same order
as in the table. If the file doesn’t contain a value for every
column or the values aren’t in the proper order, you can specify
which columns are present and the order in which they appear. If
the datafile contains fewer columns than the table, MySQL assigns
default values to columns for which no values are present in the
datafile.
LOAD
DATA
assumes that data values are
separated by tab characters and that lines end with linefeeds
(newlines). You can specify the data format explicitly if a file
doesn’t conform to these conventions.
You can indicate that data values may have quotes around them that should be stripped, and you can specify what the quote character is.
Several special escape sequences are recognized and
converted during input processing. The default escape character is
backslash (),
but you can change it if you like. The
N
sequence is interpreted to represent
a NULL
value. The ,
,
,
, \
, and