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.txt
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 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: ...
Get MySQL Cookbook 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.