Importing and Exporting Data - MySQL - page 371

Some shell commands in this chapter are so long that they're shown as you would enter
them using several lines, with a backslash character as the line-continuation character:
% prog_name \
argument1 \
argument2 ...
That works for Unix. On Windows, the continuation character is ^ (or ` for PowerShell).
Alternatively, on any platform, enter the entire command on one line:
C:\> prog_name argument1 argument2 ...
11.1. 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, which must be readable by the server. Local versus nonlocal
data loading is discussed shortly.
The MySQL utility program mysqlimport 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 (see Recipe 1.4 ).
The following list describes LOAD DATA 's general characteristics and capabilities; mysq‐
limport shares most of these behaviors. We'll note some differences as we go along, but
for the most, what can be done with LOAD DATA can be done with mysqlimport as well.