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 current database:
mysql> LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl;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:[42]
% mysqlimport --local cookbook mytbl.txtThe 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 read
"LOAD
DATA" as
"LOAD DATA or
mysqlimport.”
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 surround column values, quoting and escaping issues within
values, and NULL value representation: ...