MySQL Cookbook, 2nd Edition

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 ...