Importing Data with LOAD DATA and mysqlimport
Problem
You want to load a datafile into a table using MySQL’s built-in import capabilities.
Solution
Use the LOAD
DATA
statement or the
mysqlimport
command-line program.
Discussion
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 ...
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.